Trage query. Kan ik die sneller maken?
Voor mijn website maak ik o.a. gebruik van de volgende query:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
$sql = "SELECT
n.givn AS voornaam,
n.spfx AS tussenvoeg,
n.surn AS achternaam,
e.value AS beroep,
e.date AS periode,
i.sex AS geslacht,
i.begi_datec AS geboortedatum,
i.iid AS relatie,
f.plac AS woonplaats
FROM
ftphp__indi_name AS n
JOIN
ftphp__indi AS i
ON
i.iid = n.iid
JOIN
ftphp__even AS e
ON
e.ifid = n.iid
JOIN
ftphp__fam AS f
ON
f.husb = i.iid OR f.wife = i.iid
WHERE
e.type = '*occu*' AND e.value != ''
ORDER BY
n.surn
LIMIT " . $nStart . "," . $nMaxRecords;
$cResultaatOverzicht = mysql_query($sql);
n.givn AS voornaam,
n.spfx AS tussenvoeg,
n.surn AS achternaam,
e.value AS beroep,
e.date AS periode,
i.sex AS geslacht,
i.begi_datec AS geboortedatum,
i.iid AS relatie,
f.plac AS woonplaats
FROM
ftphp__indi_name AS n
JOIN
ftphp__indi AS i
ON
i.iid = n.iid
JOIN
ftphp__even AS e
ON
e.ifid = n.iid
JOIN
ftphp__fam AS f
ON
f.husb = i.iid OR f.wife = i.iid
WHERE
e.type = '*occu*' AND e.value != ''
ORDER BY
n.surn
LIMIT " . $nStart . "," . $nMaxRecords;
$cResultaatOverzicht = mysql_query($sql);
Mij lijkt deze query nogal "zwaar" want ik moet toch zo'n 15 tot 20 seconden wachten voor ik het resultaat op het scherm zie. Dit vind ik te lang duren, zeker bij het bladeren.
Is deze query efficiënter te maken?
George
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE n ALL NULL NULL NULL NULL 7519 Using temporary; Using filesort
1 SIMPLE i ALL NULL NULL NULL NULL 7519 Using where; Using join buffer
1 SIMPLE f ALL NULL NULL NULL NULL 3539 Using where; Using join buffer
1 SIMPLE e ALL NULL NULL NULL NULL 22514 Using where; Using join buffer
Wat kan ik hiermee?
- ftphp__indi.iid
- ftphp__even.ifid
- ftphp__fam.husb
- ftphp__fam.wife
En natuurlijk moeten de keys (.iid) steeds PRIMARY zijn.
Nog even een vraag over het gebruik van indexen:
Ik gebruik nogal wat (ingewikkelde) queries die de nodige tijd vragen om getoond te worden. Dus een goedeopzet van de tabellen en indexen is dus belangrijk.
Uit bovenstaande reacties (o.a. Henk Verhoeven) blijkt dat ik snelheid kan halen door JUISTE indexen aan te maken. Is het nu zo dat bij gebruik van JOIN indexen aanwezig moeten zijn op de zgn. "ON"-velden?
George
George van Baasbank op 22/12/2012 12:06:19:
Ja, dat is in ieder geval een goed uitgangspunt. Indexen kan je ook plaatsen op attributen die vaak achter de where voorkomen.Is het nu zo dat bij gebruik van JOIN indexen aanwezig moeten zijn op de zgn. "ON"-velden?George
Dus praktisch op elke kolom een index?
Eddy Erkelens op 22/12/2012 13:43:21:
Daar zijn weldegelijk nadelen aan verbonden: Bij een insert of update moeten al die indexen bijgewerkt (herschikt) worden om snelle index-scans te blijven waarborgen. Indexen moet je bouwen op basis van query analyse en bijvoorbeeld EXPLAIN gebruiken. De full-table-scans analyseren en proberen om te buigen naar index-range scans of index-unique scans. Indexen bijwerken is vele malen zwaarder dan het inserten van een record, hou dat in gedachten.Mijn gedachte dan: welk nadeel heeft het als je op elke kolom (behalve lange teksten) zet?Dus praktisch op elke kolom een index?
Gewijzigd op 22/12/2012 14:43:21 door John D
Dus bij veel data (die niet vernieuwd/aangevuld wordt): bijna alles INDEXeren.
Bij ook veel nieuwe data (en weinig lezen): minimale indexen?
Uiteraard alleen relevante kolommen-indexeren.
George van Baasbank op 19/12/2012 09:07:58:
Ik krijg bij explain het volgende te zien:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE n ALL NULL NULL NULL NULL 7519 Using temporary; Using filesort
1 SIMPLE i ALL NULL NULL NULL NULL 7519 Using where; Using join buffer
1 SIMPLE f ALL NULL NULL NULL NULL 3539 Using where; Using join buffer
1 SIMPLE e ALL NULL NULL NULL NULL 22514 Using where; Using join buffer
Wat kan ik hiermee?
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE n ALL NULL NULL NULL NULL 7519 Using temporary; Using filesort
1 SIMPLE i ALL NULL NULL NULL NULL 7519 Using where; Using join buffer
1 SIMPLE f ALL NULL NULL NULL NULL 3539 Using where; Using join buffer
1 SIMPLE e ALL NULL NULL NULL NULL 22514 Using where; Using join buffer
Wat kan ik hiermee?
Heel veel, je ziet dus dat het allemaal full table scans zijn.
Ik heb even terug gekeken in één van je andere topics waarin je de opbouw van de tabellen hebt uiteengezet, en ik vraag me af of je daar nog invloed kan hebben.
Wat ik daar zie dat een aantal tabellen samengestelde primary keys hebben, waar dat (naar ik vermoed) niet nodig is.
Ook kan je in de ftphp__indi_name tabel beter één index zetten op de achternaam, voornaam en tusenvoegsel.
Ook kan je de query zelf beter optimaliseren door een subquery (inline view) te gebruiken door eerst de tabel met de namen en de beroepen te selecteren en daar de limit op te zetten.
Gewijzigd op 22/12/2012 19:26:38 door Ger van Steenderen