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!

Jan hat 152 Beiträge geschrieben

33 Kommentare zu “SQL_CALC_FOUND_ROWS oder: Wie man effektiv Blätternavigationen umsetzt

  1. Marcel Eichner sagt:

    @GhostGambler: Bei so einfachen Tabellen die auch noch Keys auf jeder Spalte haben, wie bei mysqlperformance ist Count natürlich schneller – hier geht es aber definitiv um komplexere Queries als bei mysqlperforrrrrmance gezeigt.

  2. Jan sagt:

    Ah den Beitrag kannte ich noch gar nicht, aber mein Beitrag spiegelt einfach meine Ergebnisse wieder. In meinem Produktivsystem war die Anfrage raltiv komplex (inklusive LEFT JOIN und darauf folgendem GROUP BY, wo der Index nicht benutzt werden kann).

    Bei mir bringts deutlich etwas – ist wie immer im Leben auch ein bisschen Ausprobieren angesagt und nicht einfach blindes Übernehmen.

  3. maischi sagt:

    im query ist ein fehler:
    dort steht statt SQL_CALC_FOUND_ROWS fälschlicherweise .._COUNT_…

    ansonsten – danke für den beitrag 🙂

  4. mydeams sagt:

    Hallo zusammen,

    danke für den Beitrag. Leider habe ich aber noch ein kleines Verständnis Problem. Die Methode mit SQL_CALC_FOUND_ROWS hört sich ja wirklich Interessant an – aber was ist wenn ich die Zahlen vom „LIMIT“ über die „Count“-Variable Berechnen möchte. Dann könnte ich rein Theoretisch doch gar nicht diese Methode Nutzen, da ich nicht vorher weiß wie die Größe ist.

    Ich versuche mal ein Beispiel zu geben.

    // Die Gesamtgröße der Datensätze
    $number = (COUNT);

    // Berechnung der Seiten, Start-Limit und Anzeigen Pro Seite
    $seiten=floor($number/$proseite);
    $start=$_REQUEST[‚page‘] * $proseite;
    $ende = $start + $proseite;
    if ($ende>$number) { $ende = $number; }

    // Anhängende Variable die an das SQL Statement kommt
    $limiter = „LIMIT $start,$proseite“;

    In diesem Fall ist es natürlich so, dass (COUNT) über ein SQL Statement davor gezogen wird mit „SELEC cout(*)..“. Das (COUNT) selber soll jetzt nur ein Platzhalter Darstellen. Wie könnte man denn nun jetzt so eine Variante Umsetzen mit nur Einem Statement (inklusive Berechnung des LIMIT) ?

    Lieben Dank und ein schönen Tag noch
    MyDeams :o)

  5. Jan sagt:

    Ganz einfach, indem Du den Ende-Wert für deine Seiten-Navigation nicht vor dem eigentlichen SELECT ausführst.
    Derzeit berechnest Du ja erst die Anzahl und holst dann die Daten – andersrum wäre es schlauer (für das Daten-holen brauchst Du $ende nicht) und dann geht es auch so wie im Beitrag beschrieben.

  6. Jan sagt:

    Ist durchaus sinnvoll, wenn man eine Speicherengine nutzt, die das beherrscht (z.B. InnoDB).

    Edit: Obwohl: Ist das für SELECT-Anweisungen wirklich sinnvoll? Würde BEGIn / COMMIT eigentlich nur bei Schreiboperationen einsetzen.

  7. Markus sagt:

    Wie ist es eigentlich mit der Genauigkeit von FOUND_ROWS() bei mehren zeitgleichen Zugriffen auf die Datenbank: Wie kann ich sicher sein, dass sich FOUND_ROWS() auf den richtigen Query bezieht?

  8. Jan sagt:

    Man kann sich deshalb sicher sein, weil MySQL das FOUND_ROWS() auf die aktuelle DB-Verbindung bezieht. Da bei jedem Seitenaufruf eine neue Verbindung aufgemacht wird, die erst nach der Abarbeitung wieder geschlossen wird, gibt es diesbezüglich keine Probleme.

  9. manuel sagt:

    Meistens ist darüberhinaus die Gesamtzahl an Treffern gar nicht interessant, sondern nur ob es eine nächste Seite gibt oder nicht.

    Damit kann dann eine einfache „vorherige nächste“ Navigation aufgebaut werden.

    Um das zu erreichen wird einfach immer eine Zeile mehr im Limit selektiert die später nicht angezeigt wird, sondern nur zum ermitteln dient ob es eine nächste Seite gibt oder nicht….

  10. Jan sagt:

    Ja kann ja jeder machen, wie er möchte, aber wenn man einmal in der Situation war, dass man weiß, dass auf Seite 8 der Artikel war, den man nun sucht, weiß man, wie nervig es ist, sich durchzuklicken.
    Usability ist also besser, wenn alle Seiten angegeben werden und zusätzlich ein Vor- und Zurückbutton.

  11. n@3k sagt:

    Ich hab bis jetzt Variante 1 genutzt, bewege mich aber mit mysql_data_seek() im Ergebnis. So muss ich wirklich nur die Daten an PHP übergeben, die ich wirklich benötige und führe nur ein SQL-Statement aus.

    vereinfachtes Beispiel:
    mysql_query(„…“) // ohne LIMIT 15,20
    mysql_data_seek($db, 15);
    for($i = 0; $i

  12. n@3k sagt:

    Mein vorheriger Kommentar wird im FF nur zur Hälfte angezeigt. Ich vermute, dass ein verwendetes [Größer Zeichen] nicht als Entität ausgegeben wird.

  13. Jan sagt:

    Korrekt, WordPress ist doof, wenns um Code geht. Kannst Du bitte nochmal posten mit > als Größerzeichen und < als Kleinerzeichen?

  14. n@3k sagt:

    Na logo – np

    Ich hab bis jetzt Variante 1 genutzt, bewege mich aber mit mysql_data_seek() im Ergebnis. So muss ich wirklich nur die Daten an PHP übergeben, die ich wirklich benötige und führe nur ein SQL-Statement aus.

    vereinfachtes Beispiel:
    mysql_query(”…”) // ohne LIMIT 15,20
    mysql_data_seek($db, 15);
    for($i = 0; $i kleinergleich 20; $i++)
    {
    mysql_fetch_row();
    do_what_ever();
    }

    Ist wahrscheinlich langsamer als Varinate 3, zumindest wenn aufwendig sortiert wird, da ja alle Datensätze sortiert werden müssen und nicht nur die ersten 35.
    (Hat das mal jemand probiert/gemessen?)

    Ansonsten wird aber nur eine SQL Query abgeschickt und auch nur das an PHP übergeben, was benötigt wird, also 20 Zeilen.

    Werd Variante 3 aber auf jeden Fall in meinen Projekten testen. Danke für den Beitrag.

  15. alex sagt:

    also der artikel ist mit vorsicht zu genissen.

    „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.“

    ist so nicht korrekt. die gesamtzahl muss nicht ermittelt werden und wird sie auch nicht 🙂

    „SQL_CALC_FOUND_ROWS“ kann meistens bedenkenlos verwendet werden, wenn in einer abfrage kein ORDER BY eingesetzt wird.
    bei einer meinen (schon optimierten) abfragen beispielsweise ist die geschwindigkeit ohne SQL_CALC_FOUND…“ ca. 0.0006 sekunden schnell. mit SQ_CALC_FOUND_ROWS dauert sie 0.0765 sekunden (127 mal langsamer!). da benutz ich doch lieber 2 mal schneller 0.0006 abfragen 🙂

    richtig eingesetzt ist diese funktion durchaus sinnvoll, aber sie gehört nicht umsonst nicht zu einer standardabfrage.

  16. Mark sagt:

    Danke für den Tipp…habe es gerade auf einer meiner Seiten getestet und im gegensatz zu meinem vorredner ändert sich bei mir die geschwindigkeit nur um 20% langsamer!

  17. Aycko sagt:

    Bei sehr großen und komplexen Resultsets empfiehlt sich auch das Cachen des gesamten Resultsets als Array. Die Blätterfunktion an sich, lässt sich dann sehr einfach via array_slice() umsetzten.

  18. mydeams sagt:

    Hallo,

    ich hatte vor geraumer Zeit schon mal ein Post geschrieben zu diesem Thema. Damals gings darum, das ich nicht 100%ig verstand wie das Genau funktioniert mit dem Einbauen.

    Als ich es dann geschafft habe – ist mir folgendes Aufgefallen. In dem Projekt wo ich diese Methode genutzt habe, werden sehr viele MySQL Abfrage erstellt. ca. 1000 besucher hat die Seite am Tag. Mir/Uns ist aufgefallen, dass die Seite bzw. die MySQL DB sich nach na Zeit aufgehangen hat. Wegen dieses befehls. Habe ich das in der alten konventionellen Art gemacht mit nem entsprechendem Count … traten die Abstürze nimmer auf.

    In der Prozessorliste von PHPMyAdmin hing er sich immer im Prozess „copy to tmp table“ auf. Ich muss dazu aber auch sagen, dass die MySQL abfrage recht mächtig war, mit LIKE, LEFT JOIN etc. pp.

    Jetzt sind die MySQL Absturz durchaus weniger geworden, aber leider noch nicht wirklich ganz weg. Weißt Du/Ihr wie so ein „copy to tmp table“ entsteht – und wie man es vermeiden kann, das sich MySQL an diesem Prozess aufhängt?
    Es ist wirklich so, das 1 Befehl sporadisch einen Datenstau verursacht. Sobald nämlich genau diesen Prozess Kille – läuft die MySQL DB wieder einwandfrei.

    Wäre sehr dankbar für ein paar nützliche Anregungen.

    bzw. sehr toller Blog – gefallen mir gut deine Beiträge 🙂

    lieben Gruß,
    mydeams

  19. Jan sagt:

    Am besten Du analysierst die Query mit EXPLAIN erstmal. Wenn in der letzten Spalte (unter anderem) „temporary“ steht, muss eine temporäre Tabelle erzeugt werden, um das Ergebnis zu ermitteln.

    Durch geschickten Einsatz von Indizes können viele (aber längst nicht alle) dieser Fälle behoben werden.

    Vielleicht postest Du auch mal die Query, dann können wir mal gucken, welche Spalten indiziert werden sollten.

  20. Dennis sagt:

    wieso bekomme ich als aussgabe: ressource id #21

    wo liegt der fehler im code:

    $gastebuch_data = mysql_query(„SELECT SQL_CALC_FOUND_ROWS * FROM `db-gastebuch` ORDER BY TIMESTAMP DESC LIMIT {$query_string}“);
    $gastebuch_num_rows = mysql_query(„SELECT FOUND_ROWS()“);

    echo $gastebuch_num_rows;

  21. Jan sagt:

    @Dennis: Ganz einfach, weil Du auf die Ressource nicht direkt zugreifen kannst. Entweder Du nutzt mysql_result oder mysql_fetch_array.

  22. tufan sagt:

    Hi,

    ich habe es beobachtet, dass SQL_CALC_FOUND_ROWS unter Umständen (dh. InnoDB, paar Mio. Eintraege) bis zu Faktor 10000 langsamer sein kann. Also, immer schön gegen Testen…

  23. name sagt:

    n@3k 4.6.2008/19:51

    PHPs mysql_query() holt bereits im Hintergrund alle Daten ab. Das Fetchen findet anschließend nur noch im Puffer auf dem Client statt. Es werden also nicht nur die von dir angenommenen Daten abgeholt. Für ungepufferte Abfragen gibt es mysql_unbuffered_query(), damit geht aber weder mysql_num_rows() noch mysql_data_seek().

Eine Antwort schreiben

Ihre E-Mail-Adresse wird nicht veröffentlicht. Benötigte Felder sind markiert mit *

You may use these HTML tags and attributes: <a href=""> <blockquote cite=""> <pre lang=""> <b> <strong> <i> <em>