My SQL bei grosser Tabelle z.T. extrem langsam -> Lösungen?

A

aargau

Erfahrenes Mitglied
Ich habe meine Bilder Tabelle mal mit 2mio "virtuellen" Einträgen erstellt um die performance zu testen.

Nun zum testaufbau:
Gallerie mit Pagefunktion (20Bilder pro Seite)
Die ausgabe der 1. Seiten Funktioniert sehr schnell und einwandfrei.
gehe ich aber in die 10 000 oder höhere Zeilen dauern die abfragen eweig.

PHP: 
$query= mysql_query("SELECT idp, thumb, comment, nschild FROM bilder WHERE user = '1' LIMIT 0,20") ;
0,002Sec (mit der gesammten PHP Page gemessen)

PHP: 
$query= mysql_query("SELECT idp, thumb, comment, nschild FROM bilder WHERE user = '1' LIMIT 2000000,20") ;
50Sec!!
Klar, eine normale Gallerie wird nie 2Mio Bilder enthalten, doch ich denke das auch selbes passieren würde bei einem User welcher 100 Bilder hat, diese aber auf die gesammte Table verteilt ist.

Die Spalten "idp" und "user" haben einen index.

Wie kriege ich das Problem in den griff?
 
Wie lange dauert es denn, alle Datensätze von User mit id 1 auszulesen?

Das Problem sind, denk ich, die Temporary Tables, die da erzeugt werden müssen. Vielleicht kannst du es so lösen:

- Selekiere alle idp's bei denen user = $uid
- Merke die idps in einem Array
- Benutze array_chunk($array, 20) und nimm daraus den Page-Index

Ich hab das noch probiert und weiß nicht, wie das ausgehen wird.

Edit: Natürlich müsstest du das gechunkte Array dann in einer Session speichern, um Performance-Gewinn zu erhalten.
 
Die idee mit dem Array klingt eigentlich schon gut, doch was ist, wenn der user zwei tabs mit zwei user gals offen hat? Dann gibt es ein riesen durcheinander. Und die Galerien in verschiedene Variabeln in der Session zu speichern wäre wohl sehr Speicherverbrauchend und entsprechend auch nicht gerade der Performance hit.

@Felix Jacobi: Ich denke für den jetzigen "Testaufbau" passen die indizes.

Ich habe nun mal testweise ein paar "user" 2 einträge in der Tabelle verteilt erstellt und erstaunlicherweise werden die Bilder so doch relativ schnell ausgegeben.
User 1 hat im moment eben knapp 2mio Bilder, ich denke dies verlangsamt das ganze extrem. Die Frage ist nur, was passiert wenn 10 oder mehr User gleichzeitig auf die Bilder zugreiffen? Ist die Disk dann zu langsam weil sie den index jedes mal neu liest?
 
Dann speicher das chunk-Array mit einem Tag in der Session:

PHP: 
$_SESSION['gal_user_' . $uid] = $chunked_array;

Edit:

aargau hat gesagt.:
Und die Galerien in verschiedene Variabeln in der Session zu speichern wäre wohl sehr Speicherverbrauchend und entsprechend auch nicht gerade der Performance hit.
Zum Vergrößern anklicken....

Wenn in den Array nur die Ids der Datensätze sind, sollte das überschaubar bleiben. Wie du selbst schon geschrieben hast, sind 2 Mio Bilder pro User wohl sehr unwahrscheinlich.

Ich stimme allerdings Felix zu, eine Datenbank sollte das splitten mittels Limit eigentlich viel besser machen, als Code in einer Hoch-Sprache.

Wie wäre es so:

- Du selektiers zunächst nur idp mit Limit und Index-Angabe
- Du läufst über alle idps und holst dir die vollen Datensätze in einem zweiten Query, bei dem aber kein Limit sonder die idp verwendet wird.

EDIT 2: Kannst du die Beispiel-DB (nur die Tabelle mit den Gallerie-Einträgen irgendwo zur Verfügung stellen? Dann könnte man da mal praktisch was probieren.
 
Zuletzt bearbeitet:
Mal ne Frage: Welchen Sinn hat dieses Query ohne order by (Seitenumbrüche sollten ja schon konsistente Ergebnisse liefern)?

Vielleicht helfen die beiden Beiträge weiter:

http://stackoverflow.com/questions/...query-with-a-large-offset-in-the-limit-clause
http://stackoverflow.com/questions/4481388/why-does-mysql-higher-limit-offset-slow-the-query-down

In beiden kommt folgender Vorschlag vor (Annahme: idp ist Primärschlüssel)

SQL: 
SELECT bilder.idp, bilder.thumb, bilder.comment, bilder.nschild
FROM (
            SELECT idp FROM bilder
            WHERE user='1'
            ORDER BY idp
            LIMIT 2000000,20
        ) AS foo
JOIN bilder ON bilder.idp=foo.idp
 
@CPoly:
Deine Query dauert leider noch länger.
Das Problem ansich liegt aber definitiv am hohen LIMIT xxx,20 bereich. Selektiere ich direkt einen idp bereich (z.B. 2000000 - 20000040) geht die query sehr schnell.
Man muss also sagen, je mehr Fotos ein User hat desto langsamer wird die Abfrage. Sinnvoll wäre hier vieleicht ein Limit von 1000 Fotos pro User oder so, damit dies keine Probleme macht.
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück