SQL_CALC_FOUND_ROWS oder: Wie man effektiv Blätternavigationen umsetzt

Ich optimiere derzeitig sehr fleißig an meinen Projekten, da ich die tolle Logfunktion log-queries-not-using-indexes für mich entdeckt hab (Teil vom Slow-Query-Log). Im Zuge dessen habe ich einige SQL-Abfragen entdeckt, die SQL_CALC_FOUND_ROWS nutzen (vor allem von eingesetzten Fremdscripten, z.B. WordPress). Ich wollte nun also mal wissen, wozu das gut ist – und das soll hier beschrieben werden, weil es eine ganz tolle Funktion ist.

Erstmal zum Einsatzgebiet:
Bestimmt kennt jeder solche Blätternavigationen, um Suchergebnisse darzustellen, den Inhalt von Kategorien, Forenbeiträgen usw.
Ziel ist es eine erhöhte Übersicht zu schaffen, indem nur wenige Datensätze einer Gesamtmenge dargestellt werden. Das berühmteste Beispiel sind wohl die Google-Suchergebnisse. Über eine Einstellung kann dort vom Nutzer festgelegt werden, wie viele Ergebnisse pro Seite angezeigt werden sollen (Standard ist 10). Man stelle sich nur vor, wenn 1 Mio Ergebnisse auf einer Seite hingeklatscht würden. Nicht nur, dass die Übersicht flöten ginge, sondern auch die Ladezeit wäre enorm (und das auch noch ohne größeren Nutzen).

Die Einschränkung der Ergebnismenge wird normalerweise per LIMIT x,y in SQL-Abfragen umgesetzt. x ist dabei der Startwert (Offset) und y die Anzahl der zurückzugebenden Datensätze.
Beispiel:

SELECT ID,name,kategorie
   FROM tabelle INNER JOIN kategorien
   ON tabelle.kategorien_ID=kategorien.ID
   WHERE spalte1 IN (SELECT ID FROM andereTabelle WHERE email!='')
   ORDER BY zeit DESC LIMIT 15,20

Ist extra eine etwas komplexere Query, um die Vorteile von SQL_CALC_FOUND_ROWS gleich aufzeigen zu können. Die Anfrage liefert also 20 Datensätze beginnend ab dem 15. (basierend auf der absteigenden Sortierung nach der Spalte zeit). Sagen wir, dass die Abfrage 0,5 Sekunden dauert (Suchabfragen sind oft recht aufwändig – noch schlimmer wirds unter Umständen, wenn man mit LIKE arbeitet (und auf beiden Seiten des Wortes %-Wildcards benutzt, denn dann kann kein Index genutzt werden) – oder die MATCH-AGAINST-Funktion einsetzt).

Gut, nun werden die 20 Datensätze aufgelistet, bis hierhin nix Neues. Jetzt gilt es aber eine Blätter-Navigation einzubauen, um zur Seite zu kommen, wo die Ergebnisse vor Datensatz 15 anzusehen bzw. nach der 35. Und dazu müsste man wissen, wie viele Datensätze die Anfrage insgesamt (also ohne das LIMIT) zurückliefert.
Es gibt nun 3 Wege:

1. Das LIMIT streichen und in PHP eine Zählvariable das LIMIT übernehmen lassen (solange diese kleiner als 15 ist, continue; – wenn größer als 15+20, dann break;
2. das LIMIT drin lassen und für die Ermittlung der Gesamtanzahl die Query mit der gleichen Bedingung erneut ausführen, nur diesmal ohne ORDER und indem COUNT(*) selektiert wird
3. das LIMIT drin lassen und trotzdem an die Gesamtanzahl gelangen.

Welcher Weg klingt wohl am elegantesten? Der Dritte.
Aber vorher eine kurze Erläuterung zum ersten Weg: Selbst wenn für das Sortieren ein Index genutzt werden kann, müssten alle passenden Datensätze sortiert werden, statt nur diejenigen bis die Datensätze 15 – 35 feststehen. Das ist unnötiger Overhead. Per Index geht das recht schnell, denn im Balanced Tree findet man schnell die größten Werte und kann dann eben auch recht schnell aufhören, trotzdem ist es verschwenderisch. Außerdem verursacht 1. viel mehr Traffic bzw. Ressourcenverbrauch, da sämtliche Daten an PHP geschickt werden müssen – obwohl die wenigsten gebraucht werden. Wir benötigen ja eigentlich nur die Anzahl.

Der 2. Ansatz ist schon etwas geschickter als 1., jedoch muss MySQL den Großteil der Arbeit dann 2 mal machen, denn prinzipiell muss das gesamte Result-Set erstmal wieder selektiert werden (inkl. Joins und WHERE-Bedingungen). Nur die Menge der gesendeten Daten verringert sich (nur noch eine Zahl aus dem COUNT(*)) und das Sortieren fällt weg (brauchen wir ja nicht für die Gesamtanzahlermittlung).

Und nun kommt der 3. Weg ins Spiel. SQL_CALC_FOUND_ROWS löst unser Problemchen optimal. Damit ist es möglich, die Gesamtmenge einer Abfrage, die durch LIMIT nur eine Teilmenge der insgesamt selektierten Datensätze zurückliefert, zu ermitteln. Dazu müssen wir unsere Abfrage leicht verändern, das Schlüsselwort SQL_CALC_FOUND_ROWS muss hinzugefügt werden:

SELECT SQL_CALC_FOUND_ROWS ID,name,kategorie
   FROM tabelle INNER JOIN kategorien
   ON tabelle.kategorien_ID=kategorien.ID
   WHERE spalte1 IN (SELECT ID FROM andereTabelle WHERE email!='')
   ORDER BY zeit DESC LIMIT 15,20

Das verzögert die Abfrage keineswegs, denn auf dem Weg zum gewünschten Ergebnis muss ja sowieso erstmal die Gesamtmenge ermittelt werden – da erfordert es kaum Zeit, diese Zahl kurzerhand in eine Variable zu schreiben. Auch das ResultSet an sich verändert sich nicht – Anpassungen am PHP-Code sind also nicht erforderlich.
Wie kommen wir nun aber an die Zahl der Gesamtdatensätze dran? Dafür ist eine weitere SQL-Abfrage nötig:

SELECT FOUND_ROWS()

Diese gibt nun einfach die vorher zwischengespeicherte Anzahl zurück. Dahinter steckt demzufolge minimaler Aufwand.
Wichtig ist aber, dass der zwischengespeicherte Wert flüchtig ist. FOUND_ROWS() sollte deshalb direkt nach der LIMIT-Abfrage ausgeführt werden und dann speichert man sich die Gesamtanzahl einfach in einer PHP-Variable, auf die dann weiter unten zugegriffen werden kann.

Dies erspart jede Menge Traffic (siehe 1. Weg oben) bzw. eine aufwändige zweite Abfrage (siehe 2. Weg). Und senkt natürlich die Ausführungszeit und die Last, da kaum Mehrbelastung gegenüber der LIMIT-Abfrage auftritt (wie gesagt: die Gesamtanzahl muss zwischendurch sowieso ermittelt werden, da ist es ein Leichtes diese mal eben zwischen zu speichern).
Zusätzlich besteht somit nicht mehr die Gefahr des 2. Weges, dass man Änderungen an der Query in der LIMIT-Abfrage sowie in der Zählabfrage setzen muss. Denn es kann ja mal eine zusätzliche WHERE-Bedingung hinzukommen und wenn man die in der Zählabfrage vergisst nachzuziehen, ist die Anzahl der Seiten nicht mehr korrekt. Mit SQL_CALC_FOUND_ROWS wird demzufolge auch die Möglichkeit eines Fehlers gesenkt, da es nur eine Abfrage gibt, die verändert werden muss (falls es eine Änderung gibt).

Ich hoffe der Beitrag war verständlich. Fragen und Anregungen sind aber wie immer in den Kommentaren erwünscht!