Oude SQL UNION query herbouwen
Ik ben begonnen aan een oud systeem van een klant weer werkend te maken en traagheid en fouten er uit te halen.
Maar nu kom ik een query tegen welke echt belachelijk lang er over doet om te laden (lees meer dan 27 sec. gemiddeld), en ik heb al meerdere manieren geprobeerd maar ik krijg eigenlijk nooit het zelfde resultaat terug.
Hoe kan ik deze query veranderen zodat hij gewoon sneller wordt?
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SELECT
inkoop_caravan.Inkoop_ID,
CONCAT_WS(' ', caravan.Merk, caravan.Type),
CONCAT_WS(' ', klant.tussenvoegsel, klant.Achternaam),
'-',
inkoop_caravan.Inkoopprijs,
inkoop_caravan.Omschrijving,
inkoop_caravan.InkoopDatum,
klant.Klant_ID,
caravan.Caravan_ID
FROM caravan, klant, werknemer, inkoop_caravan
WHERE
(caravan.Caravan_ID = inkoop_caravan.Caravan_ID OR inkoop_caravan.Caravan_ID IS NULL)
AND (klant.Klant_ID = inkoop_caravan.Klant_ID AND inkoop_caravan.Werknemer_ID IS NULL)
AND (inkoop_caravan.Werknemer_ID IS NULL)
AND (caravan.Chassisnummer LIKE '%".mysql_real_escape_string(@$_POST[1])."%' OR caravan.Chassisnummer IS NULL)
AND (CONCAT_WS(' ', caravan.Merk, caravan.Type) LIKE '%".mysql_real_escape_string(@$_POST[6])."%')
AND (klant.Achternaam LIKE '%".mysql_real_escape_string(@$_POST[2])."%' OR klant.Achternaam IS NULL)
AND (inkoop_caravan.Inkoopprijs LIKE '%".mysql_real_escape_string(@$_POST[4])."%' OR inkoop_caravan.Inkoopprijs IS NULL)
AND (inkoop_caravan.Omschrijving LIKE '%".mysql_real_escape_string(@$_POST[5])."%' OR inkoop_caravan.Omschrijving IS NULL)
AND (inkoop_caravan.Inkoopdatum LIKE '%".mysql_real_escape_string(@$_POST['datum'])."%' OR inkoop_caravan.Inkoopdatum IS NULL)
UNION
SELECT
inkoop_caravan.Inkoop_ID,
CONCAT_WS(' ', caravan.Merk, caravan.Type),
'-',
werknemer.Naam,
inkoop_caravan.Inkoopprijs,
inkoop_caravan.Omschrijving,
inkoop_caravan.InkoopDatum,
'-',
caravan.Caravan_ID
FROM caravan, klant, werknemer, inkoop_caravan
WHERE
(caravan.Caravan_ID = inkoop_caravan.Caravan_ID OR inkoop_caravan.Caravan_ID IS NULL)
AND (inkoop_caravan.Klant_ID IS NULL)
AND (werknemer.Werknemer_ID = inkoop_caravan.Werknemer_ID AND inkoop_caravan.Klant_ID IS NULL)
AND (caravan.Chassisnummer LIKE '%".mysql_real_escape_string(@$_POST[1])."%' OR caravan.Chassisnummer IS NULL)
AND (CONCAT_WS(' ', caravan.Merk, caravan.Type) LIKE '%".mysql_real_escape_string(@$_POST[6])."%')
AND (werknemer.Achternaam LIKE '%".mysql_real_escape_string(@$_POST[3])."%' OR werknemer.Achternaam IS NULL)
AND (inkoop_caravan.Inkoopprijs LIKE '%".mysql_real_escape_string(@$_POST[4])."%' OR inkoop_caravan.Inkoopprijs IS NULL)
AND (inkoop_caravan.Omschrijving LIKE '%".mysql_real_escape_string(@$_POST[5])."%' OR inkoop_caravan.Omschrijving IS NULL)
AND (inkoop_caravan.Inkoopdatum LIKE '%".mysql_real_escape_string(@$_POST['datum'])."%' OR inkoop_caravan.Inkoopdatum IS NULL)
UNION
SELECT
inkoop_caravan.Inkoop_ID,
CONCAT_WS(' ', caravan.Merk, caravan.Type),
CONCAT_WS(' ', klant.tussenvoegsel, klant.Achternaam),
werknemer.Naam,
inkoop_caravan.Inkoopprijs,
inkoop_caravan.Omschrijving,
inkoop_caravan.InkoopDatum,
klant.Klant_ID,
caravan.Caravan_ID
FROM caravan, klant, werknemer, inkoop_caravan
WHERE
(caravan.Caravan_ID = inkoop_caravan.Caravan_ID OR inkoop_caravan.Caravan_ID IS NULL)
AND (werknemer.Werknemer_ID = inkoop_caravan.Werknemer_ID AND klant.Klant_ID = inkoop_caravan.Klant_ID)
AND (caravan.Chassisnummer LIKE '%".mysql_real_escape_string(@$_POST[1])."%' OR caravan.Chassisnummer IS NULL)
AND (CONCAT_WS(' ', caravan.Merk, caravan.Type) LIKE '%".mysql_real_escape_string(@$_POST[6])."%')
AND (klant.Achternaam LIKE '%".mysql_real_escape_string(@$_POST[2])."%' OR klant.Achternaam IS NULL)
AND (werknemer.Achternaam LIKE '%".mysql_real_escape_string(@$_POST[3])."%' OR werknemer.Achternaam IS NULL)
AND (inkoop_caravan.Inkoopprijs LIKE '%".mysql_real_escape_string(@$_POST[4])."%' OR inkoop_caravan.Inkoopprijs IS NULL)
AND (inkoop_caravan.Omschrijving LIKE '%".mysql_real_escape_string(@$_POST[5])."%' OR inkoop_caravan.Omschrijving IS NULL)
AND (inkoop_caravan.Inkoopdatum LIKE '%".mysql_real_escape_string(@$_POST['datum'])."%' OR inkoop_caravan.Inkoopdatum IS NULL)
ORDER BY InkoopDatum DESC LIMIT 0,30
inkoop_caravan.Inkoop_ID,
CONCAT_WS(' ', caravan.Merk, caravan.Type),
CONCAT_WS(' ', klant.tussenvoegsel, klant.Achternaam),
'-',
inkoop_caravan.Inkoopprijs,
inkoop_caravan.Omschrijving,
inkoop_caravan.InkoopDatum,
klant.Klant_ID,
caravan.Caravan_ID
FROM caravan, klant, werknemer, inkoop_caravan
WHERE
(caravan.Caravan_ID = inkoop_caravan.Caravan_ID OR inkoop_caravan.Caravan_ID IS NULL)
AND (klant.Klant_ID = inkoop_caravan.Klant_ID AND inkoop_caravan.Werknemer_ID IS NULL)
AND (inkoop_caravan.Werknemer_ID IS NULL)
AND (caravan.Chassisnummer LIKE '%".mysql_real_escape_string(@$_POST[1])."%' OR caravan.Chassisnummer IS NULL)
AND (CONCAT_WS(' ', caravan.Merk, caravan.Type) LIKE '%".mysql_real_escape_string(@$_POST[6])."%')
AND (klant.Achternaam LIKE '%".mysql_real_escape_string(@$_POST[2])."%' OR klant.Achternaam IS NULL)
AND (inkoop_caravan.Inkoopprijs LIKE '%".mysql_real_escape_string(@$_POST[4])."%' OR inkoop_caravan.Inkoopprijs IS NULL)
AND (inkoop_caravan.Omschrijving LIKE '%".mysql_real_escape_string(@$_POST[5])."%' OR inkoop_caravan.Omschrijving IS NULL)
AND (inkoop_caravan.Inkoopdatum LIKE '%".mysql_real_escape_string(@$_POST['datum'])."%' OR inkoop_caravan.Inkoopdatum IS NULL)
UNION
SELECT
inkoop_caravan.Inkoop_ID,
CONCAT_WS(' ', caravan.Merk, caravan.Type),
'-',
werknemer.Naam,
inkoop_caravan.Inkoopprijs,
inkoop_caravan.Omschrijving,
inkoop_caravan.InkoopDatum,
'-',
caravan.Caravan_ID
FROM caravan, klant, werknemer, inkoop_caravan
WHERE
(caravan.Caravan_ID = inkoop_caravan.Caravan_ID OR inkoop_caravan.Caravan_ID IS NULL)
AND (inkoop_caravan.Klant_ID IS NULL)
AND (werknemer.Werknemer_ID = inkoop_caravan.Werknemer_ID AND inkoop_caravan.Klant_ID IS NULL)
AND (caravan.Chassisnummer LIKE '%".mysql_real_escape_string(@$_POST[1])."%' OR caravan.Chassisnummer IS NULL)
AND (CONCAT_WS(' ', caravan.Merk, caravan.Type) LIKE '%".mysql_real_escape_string(@$_POST[6])."%')
AND (werknemer.Achternaam LIKE '%".mysql_real_escape_string(@$_POST[3])."%' OR werknemer.Achternaam IS NULL)
AND (inkoop_caravan.Inkoopprijs LIKE '%".mysql_real_escape_string(@$_POST[4])."%' OR inkoop_caravan.Inkoopprijs IS NULL)
AND (inkoop_caravan.Omschrijving LIKE '%".mysql_real_escape_string(@$_POST[5])."%' OR inkoop_caravan.Omschrijving IS NULL)
AND (inkoop_caravan.Inkoopdatum LIKE '%".mysql_real_escape_string(@$_POST['datum'])."%' OR inkoop_caravan.Inkoopdatum IS NULL)
UNION
SELECT
inkoop_caravan.Inkoop_ID,
CONCAT_WS(' ', caravan.Merk, caravan.Type),
CONCAT_WS(' ', klant.tussenvoegsel, klant.Achternaam),
werknemer.Naam,
inkoop_caravan.Inkoopprijs,
inkoop_caravan.Omschrijving,
inkoop_caravan.InkoopDatum,
klant.Klant_ID,
caravan.Caravan_ID
FROM caravan, klant, werknemer, inkoop_caravan
WHERE
(caravan.Caravan_ID = inkoop_caravan.Caravan_ID OR inkoop_caravan.Caravan_ID IS NULL)
AND (werknemer.Werknemer_ID = inkoop_caravan.Werknemer_ID AND klant.Klant_ID = inkoop_caravan.Klant_ID)
AND (caravan.Chassisnummer LIKE '%".mysql_real_escape_string(@$_POST[1])."%' OR caravan.Chassisnummer IS NULL)
AND (CONCAT_WS(' ', caravan.Merk, caravan.Type) LIKE '%".mysql_real_escape_string(@$_POST[6])."%')
AND (klant.Achternaam LIKE '%".mysql_real_escape_string(@$_POST[2])."%' OR klant.Achternaam IS NULL)
AND (werknemer.Achternaam LIKE '%".mysql_real_escape_string(@$_POST[3])."%' OR werknemer.Achternaam IS NULL)
AND (inkoop_caravan.Inkoopprijs LIKE '%".mysql_real_escape_string(@$_POST[4])."%' OR inkoop_caravan.Inkoopprijs IS NULL)
AND (inkoop_caravan.Omschrijving LIKE '%".mysql_real_escape_string(@$_POST[5])."%' OR inkoop_caravan.Omschrijving IS NULL)
AND (inkoop_caravan.Inkoopdatum LIKE '%".mysql_real_escape_string(@$_POST['datum'])."%' OR inkoop_caravan.Inkoopdatum IS NULL)
ORDER BY InkoopDatum DESC LIMIT 0,30
Gewijzigd op 17/04/2012 09:26:28 door DirkJan Heinen
Het meeste kom je te weten door 'EXPLAIN' voor je query te zetten en deze uit te voeren in een console of phpmyadmin.
En misschien dat het doornemen van volgend artikel je ook wat oplevert: http://www.keesschepers.nl/2011/03/02/mysql-performance-verbeteren/
Echter in jouw geval is er zeker ook een optie 2. Er worden heel veel LIKE statements gebruikt met wildcards (%) erin. Dat is vaak ook niet al te snel. In sommige gevallen zal je dat niet kunnen veranderen, maar bij een paar statements zou ik je aanraden om het aan te passen, wellicht zelfs de kolom.
inkoop_caravan.Inkoopprijs LIKE '%".mysql_real_escape_string(@$_POST[4])."%'
Als mijn intuitie goed is, dan zou een inkoopprijs nooit een string hoeven te zijn, maar een integer of een double. Hier zou je dus nooit een LIKE hoeven te gebruiken, maar ofwel een gelijkheid (=) ofwel een bandbreedte aangeven (BETWEEN ... AND ...). Dat soort vergelijkingen met getallen zijn stukken sneller dan LIKE statements.
inkoop_caravan.Inkoopdatum LIKE '%".mysql_real_escape_string(@$_POST['datum'])."%'
Een inkoopdatum zou ik verwachten als een DATE veld, ook weer niet als een string. Als je een DATE veld hebt kan je weer hetzelfde gebruiken als hierboven om het te versnellen.
Optie 3:
Je kan ook kijken of je de UNION aan kan passen. Wat je nu in feite doet is drie keer een query draaien (met al die trage LIKE vergelijkingen), terwijl het misschien ook wel in een keer kan. De joins zien er erg hetzelfde uit, dus misschien kan je het in een keer doen. Of, maak eerst de UNION op alle records of op die records die je zonder een LIKE vergelijking selecteert en doe de trage selecties pas op de complete dataset. De truuk is namelijk om zo snel mogelijk het aantal records te verminderen.
Gewijzigd op 17/04/2012 10:38:56 door Erwin H
Quote:
FROM caravan, klant, werknemer, inkoop_caravan
kan omschrijven naar LEFT JOINS zodat je niet het cartesisch product krijgt (tenzij je die nodig hebt natuurlijk, dat kan ik zo snel niet opmaken uit je query). Daarnaast kan je foreign keys aanleggen (weet niet of dit helpt bij efficiency verbeteringen eerlijk gezegd maar in ieder geval wel voor de integriteit van je data).
