CBlog

Hätte ich diesen Artikel zwei Stunden früher gepostet, hätte man ihn für einen Aprilscherz gehalten: Ich habe gestern eine MySQL-Query bei gleichem Ergebnis um den Faktor 1000 beschleunigt. Das Ganze ohne irgendwelche Änderungen an der Datenbank oder der MySQL-Konfiguration, sondern nur durch Änderung der Query.

Das ist die ursprüngliche Query (etwas vereinfacht, im Original wurden noch ein paar mehr Felder SELECTed):

SELECT 
a.id,a.rubrik,a.sort,
b.id as rid,b.verfall as rverfall,
c.id as sid,c.verfall as sverfall,
d.id as kid,d.verfall as kverfall
FROM a,b,c,d
WHERE a.rubrik=24 AND
(a.id = b.id OR a.id = c.id OR a.id = d.id) AND 
(b.verfall >=now() OR c.verfall >=now() OR d.verfall >=now())
GROUP BY a.id
ORDER BY a.sort;

Laufzeit dieser Abfrage sind gut 3 Sekunden bei einem recht kleinen Datenbestand (b, c und d haben jeweils rund 100 Datensätze, a hat "immerhin" 380). Das Ergebnis der Query sind lächerliche 8 Datensätze.

Bei einem so kleinen Datenbestand ist die hohe Laufzeit doch recht verwunderlich. Ich wage zu behaupten, dass man die Daten per RTP (Rauchzeichen Transfer Prototokoll ;-) schneller aus der Datenbank bekommen hätte...

Sieht man sich die Abfrage mit EXPLAIN an, sieht man einen Teil der Ursache: Es wird eine temporäre Tabelle angelegt. (Waaas?! Für 8 Datensätze?!). Verständlich wird das Problem, wenn man einmal das "GROUP BY" weglässt. Das Ergebnis enthält dann rund 15.000 Datensätze - davon 14.992 Duplikate, die durch die impliziten JOINs ("WHERE ... a.oid=b.oid") erzeugt werden. Dass man für diese Datenmenge eine temporäre Tabelle braucht, ist schon weniger verwunderlich ;-) aber trotzdem nicht akzeptabel.

Die überarbeitete Query sieht dann so aus:

SELECT a.rubrik,a.id,a.tabname,a.sort,
b.id as rid,b.verfall as rverfall
FROM a,b
WHERE a.rubrik=24 AND a.id = b.id AND b.verfall >=now()
UNION
SELECT a.rubrik,a.id,a.tabname,a.sort,
c.id as sid,c.verfall as sverfall
FROM a,c
WHERE a.rubrik=24 AND a.id = c.id AND c.verfall >=now()
UNION
SELECT a.rubrik,a.id,a.tabname,a.sort,
d.id as kid,d.verfall as kverfall
FROM a,d
WHERE a.rubrik=24 AND a.id = d.id AND d.verfall >=now() 
ORDER BY a.sort;

Wie man sieht, braucht diese Abfrage kein GROUP BY, weil sie keine überflüssigen Duplikate produziert. Das wirkt sich auch sehr positiv auf die Laufzeit aus - nur noch 0,003 Sekunden und somit ein Tausendstel der alten Query :-)

Danke, Kris für die MySQL-Dienstage in Deiner Zeit in Karlsruhe, bei denen man u. a. solche Dinge lernen konnte. Ja, ich lese Dein Blog immer noch ;-) - unter anderem wegen der hervorragenden technischen Artikel.

Mit etwas Verspätung (wegen Zeitmangel), dafür aber mit Grüßen vom Osterhasen veröffentliche ich heute die SQL-Abfragen, um die Mailkonfiguration von Qmail / Plesk nach Postfix mit MySQL-Backend zu migrieren. Einige der Queries haben durchaus Chancen auf den Longest-SQL-Query-of-the-year-Award ;-)

Alle Queries wurden bei einer echten Migration getestet und funktionieren. Zur Verwaltung der Mailkonfiguration nach der Migration zu Postfix empfehle ich Postfixadmin (RPMs), bei dem ich auch seit einiger Zeit mitprogrammiere.

A bit late (because a lack of time), but with greetings from the easter-bunny, I publish the SQL queries that are needed to migrate from Qmail / Plesk to Postfix with MySQL backend. Some of the queries have good chances to win the longest-SQL-query-of-the-year award ;-)

All queries were tested while a real server migration and work. To manage your mail addresses after migrating to postfix, I recommend Postfixadmin (RPMs) on which I'm co-developer since some time.

Die SQL-Queries und einige Details stehen im vollständigen Artikel - bitte weiterlesen...

The SQL queries and some more details are available in the full article - read on...