SQL Optimierung mit COUNT DISTINCT
Es ist nicht das erste mal: Man programmiert etwas und wenn es denn im Einsatz steht, läuft es plötzlich nicht mehr so schnell. Entweder weil mehr Benutzer oder mehr Daten da sind, als man erwartet hätte. So passiert bei unserem Newsletter Tracking. Die Statistik braucht extrem lange, bis die interessanten Zahlen erscheinen. Ein paar wenige Buchstaben verkürzen die Wartezeit um das 546-fache!
Vorher
Eine Abfrage holt Informationen aus verschiedenen Tabellen. Zuerst aus den documents, dort finden sich die Newsletter. Dann aus der Tabelle email_log, welche die versandten E-Mails enthält. Und zu guter letzt die selbst gemachte Tracking Tabelle, die in dem Fall object_3 heisst, weil wir dafür ein Pimcore Objekt erstellt haben.
Das Problem: Die Tracking Tabelle enthält pro öffnen eines E-Mails, bzw. pro Anzeige des Tracking-Bildes, einen Eintrag. Man muss also nach dem User gruppieren, um eine aussagekräftige Anzahl zu erhalten.
Das geschah bisher so: Die Quelltabelle ist eine mit einem Select-Befehl temporär erstelle Tabelle, die pro Eintrag es Trackings eine Zeile hat. Diese Zeilen werden dann in der eigentlichen Abfrage grupiert und zusammengezählt. Nun hat diese temporäre Tabelle bei 10 an ca. 2000 Personen versandte E-Mails dementsprechend Zeilen. Die Abfrage dauert zwischen 4 und 5 Minuten. Viel zu lange!
SELECT newsletter,
COUNT(anzahl) as open,
(SELECT COUNT(documentId) FROM email_log WHERE documentId = tbl.id) AS empfaenger,
FORMAT ((100/(SELECT COUNT(documentId) FROM email_log WHERE documentId = tbl.id) )*COUNT(anzahl), 2) as prozent
FROM (
SELECT doc.id, doc.key AS newsletter, COUNT(id) AS anzahl
FROM object_3 tracking
JOIN documents AS doc ON doc.id = tracking.newsletterdocument__id
WHERE doc.key != "confirmation"
GROUP BY doc.id, doc.key, tracking.newsletteruser__id
ORDER BY doc.key DESC
) as tbl
GROUP BY newsletterNachher
Anstatt eine jedes Tracking in einer temporären Tabelle aufzulisten, bedienen wir uns der COUNT(DISTINCT fieldname). Diese zählt die gruppierten Einträge zusammen, zählt aber jede einzigartige Zeile nur einmal. Die neue temporäre Tabelle enthält also bereits die Summe der Öffnungen. Mit ein paar wenigen Zeilen ist auch die eigentliche Abfrage wesentlich effizienter, weil sie keine abertausenden Einträge mehr gruppieren muss.
SELECT newsletter,
SUM(anzahl) as open,
(SELECT COUNT(documentId) FROM email_log WHERE documentId = tbl.id) AS empfaenger,
FORMAT ((100/(SELECT COUNT(documentId) FROM email_log WHERE documentId = tbl.id) )*SUM(anzahl), 2) as prozent
FROM (
SELECT doc.id, doc.key AS newsletter, COUNT(DISTINCT tracking.newsletteruser__id) AS anzahl
FROM object_3 tracking
JOIN documents AS doc ON doc.id = tracking.newsletterdocument__id
WHERE doc.key != "confirmation"
GROUP BY doc.id, doc.key
ORDER BY doc.key DESC
) as tbl
GROUP BY newsletterDie Unterschiede in den zwei Abfragen befinden sich lediglich in der Zeile 11, wo nicht mehr nach dem tracking gruppiert wird, und Zeile 7, wo der COUNT mit DISTINCT ergänzt wird.
Das Ergebnis ist eine Abfrage, die knapp eine halbe Sekunde dauert. Das ist somit 546-mal schneller!