MySQL Query optimizing

Queries optimaliseren

Iedere ontwikkelaar heeft er al wel eens naar gezocht, hoe kan ik mijn queries sneller krijgen? Bij een project dat we hebben geerfd van een ander bureau heb ik een van de queries met factor 159 kunnen versnellen.

Rond de middag kwam er een telefoontje van een klant. Ze hadden een probleem met een excel download die geen resultaat gaf en traag was bij grote selecties.

Hallo Joris, kan je even kijken naar de excel download? We krijgen soms een leeg bestand.

Op zoek naar de oorzaak dan maar, misschien heb ik een te oude versie van de excelparser geïnstalleerd? Nee, die is recent. Misschien is het probleem een timeout? Kan de query iets sneller?

SELECT c1,c2,c3,c4, a1, a2, a3, a4, b1, 
b2 FROM table1 a , table2, 
(SELECT table4.col1 d1, table4.col2 d2 FROM table4 WHERE table4.type = 'test1' AND table4.col3 = a.a1 ), 
(SELECT table4.col1 d3, table4.col2 d4 FROM table4 WHERE table4.type = 'test2' AND table4.col3 = a.a1 ) b RIGHT JOIN table3 c 
WHERE a.a1 = b.b1 AND c1 = b1 AND c4 < '2012-07-01'

Ik denk dat daar nog iets aan kan veranderd worden! Maar we zullen even beginnen met die query op de test database te draaien met behulp van een EXPLAIN. Dat zal al wel voor een aangeven wat er allemaal fout gaat. Om te beginnen lijkt het met al niet zo fijn dat er 2 tabellen volledig worden opgehaald.

De manier waarop ik naar queries kijk is: eliminate early, dat kan hier dus beter! Hoe minder resultaten er moeten doorzocht worden, hoe beter. En die subselects? Daar ben ik ook geen fan van.

SELECT `c1`, `c2`, `c3`, `c3`,
  `a1`, `a2`, `a3`, `a4`, 
  `b1`, `b2`, `d`.`col1` AS `d1`, `d`.`col2` AS `d2`,  
  `e`.`col1` AS `d3`, `e`.`col2` AS `d4`
FROM `table1` AS `a`
  LEFT JOIN `table2` AS `b` ON (`a`.`a1` = `b`.`b1`)
  RIGHT JOIN `table3` AS `c` ON (`c`.`c1` = `b`.`b1`)
  LEFT JOIN `table4` AS `d` ON (`d`.`col3` = `a`.`a1` AND `d`.`type` = 'test1')
  LEFT JOIN `table4` AS `e` ON (`e`.`col3` = `a`.`a1` AND `e`.`type` = 'test1')
WHERE `c`.`c4` < '2012-07-01'

Niet alleen is de query hierdoor een heel pak leesbaarder geworden, op deze manier zijn er ook een heel pak minder rijen die moeten aangesproken worden. De originele query had voor 3 tabellen een select_type: DERIVED bij de tweede query zijn alle tabellen omgezet naar SIMPLE. Je kan op bijvoorbeeld [mysqlperformanceblog.com] meer lezen over de verschillen.

In de productieomgeving heeft deze query een resultaat van +-4000 rijen versneld van gemiddeld 230 seconden naar ongeveer 1.5 seconde.