Oude SQL UNION query herbouwen

Door DirkJan Heinen op 17-04-2012 09:26 gewijzigd op 17-04-2012 09:26

2.524 views

Beste Leden,

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?


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

kees Schepers

17-04-2012 09:32

Waarschijnlijk win je al een hoop door indexen te leggen op een of meerdere velden die voorkomen in de where criteria. En ook order by.

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/

Erwin H

17-04-2012 09:57 gewijzigd op 17-04-2012 10:38

Zoals Kees al zegt, optie 1 zou moeten zijn om naar de indexen te kijken, dat scheelt vaak al een hoop.
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.

Jan geen

17-04-2012 13:01

Sowieso de reeds genoemde opties en daarnaast zou ik kijken of je

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).

Ger van Steenderen

18-04-2012 17:34

Me aansluitend bij bovenstaande reacties betekent het dus gewoon heel die query herschrijven.

Reageren

Inloggen om te reageren