Performance in "großen" Shops

[webald]

Ich habe jetzt mal einfach so versucht den Shop zu stressen und habe diesen mit 200.000 Testartikel gefüttert. Neben den Probleme mit den neuen Artikeln (viele neue Artikel führen zu langen Ladezeiten) bin ich noch über ein Problem gestolpert: Der Shop ruft viel zu viele Daten unnötig ab und nutz diese nicht.

Konkret:
Habe eine Kategorie mit 150.000 Artikeln (ich weiss, unrealistisch, hat niemand). Bei "neue Artikel" und Massenimport aber dann doch gar nicht mehr so unrealistisch.
Ruft man diese Kategorie im Shop auf, dauert es ewig bis die Seite geladen wird und man läuft mit hoher Wahrscheinlichkeit in einen Timeout.

Der Grund ist einfach: Es werden alle Artikel dieser Kategorie abgerufen, obwohl nur die ersten 20 angezeigt werden sollten. Blättert man 1 Seite weiter (Artikel 21-40) erfolgt wieder ein vollständiger Abruf.

Man müßte die SQL-Abfrage umbauen so dass nur 20 Artikel abgerufen werden.
Die erste Idee sieht in etwa so aus:

Code: SQL  [Auswählen]

SELECT * FROM products LIMIT 0,20

analog dann für die Artikel 101-120

Code: SQL  [Auswählen]

SELECT * FROM products LIMIT 101,20

Wer das mit 100.000 Artikel probiert wird feststellen, dass die Abfrage mit immer höheren Startwerten immer langsamer wird.
Man könnte aber eine indizierte Spalte zu Ermittlung des Startwertes "missbrauchen". Das sieht dann so aus:

Code: SQL  [Auswählen]

SELECT * FROM products WHERE products_id > 50000 ORDER BY products_id LIMIT 20

Setzt man nun für die neue Abfrage products_id immer auf den letzten angezeigten Wert von products_id erhält mein eine performnte Abfrage auch für viele Artikel.

Linkback: https://www.modified-shop.org/forum/index.php?topic=33099.0

[Matt]

Siehe dazu auch http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ und den Ansatz im letzten Codeschnipsel, mit der man die letzte ID nicht mal kennen muss.

[webald]

Hi Matt,

sieht so ähnlich aus wie man das für den MS SQL-Server machen muss, wenn man Datensätze von x-y haben will (der kennt nämlich kein Limit, sondern nur Top).

So müßte das ja z. B. für products aussehen:

Code: SQL  [Auswählen]

SELECT * FROM (SELECT products_id FROM products ORDER BY products_id LIMIT 150000,20) o JOIN products p ON p.products_id = o.products_id ORDER BY p.products_id

Falls Du eine Tabelle mit 200000+ zur Verfügung hast, wäre mal interessant was da rauskommt. Bei mir schwankt das zwischen 1,5 und 2,9 Sekunden.

Dagegen mit

Code: SQL  [Auswählen]

    SELECT * FROM products WHERE products_id > 150000 ORDER BY products_id LIMIT 20

bekomme ich Zeiten zwischen 0,05 und 0,4 Sekunden.

Für mich ist das eigentlich auch logisch, da die Subquery ja genau das Limit-Problem erneut ausführt.

[Matt]

Ich hab mir mal gerade das Beispiel nachgebaut, das im verlinkten Artikel enthalten ist. Und ja, das stimmt. Die ID-Geschichte ist natürlich nochmal deutlich schneller (Unterschied hier 0.0443 vs. 0.0005 Sekunden, einfach nur ein 'blanker' Limit kommt sogar auf 0.2315 Sekunden).

Das der Unterschied so krass ist hätte ich nicht gedacht. Vorteil an der Lösung mit dem Subselect ist halt, dass er ohne große Änderungen eingebaut werden kann und direkt was bringt. Aber wenn man da eh grad umbaut könnte man natürlich auch gleich deine Lösung implementieren.

[p3e]

Das geht aber nur, wenn man grundsätzlich nach products_id sortiert ausgeben will.
Gerade bei großen Datenmengen will man die wichtigsten oder umsatzstärksten Artikel zu Anfang gelistet haben. Dazu nutzt man dann die Reihung, die anders als die products_id durchaus mehrfach vergeben sein kann.

[webald]

Es gibt aber nicht nur einen Index auf products_id, sondern auch auf andere wesentlichen Spalten wie products_date_added, products_model, products_price oder manufacturer_id, so dass auch Suchen nach diesen Werten entsprechend schnell durchgeführt werden.

[saithis]

Ein Index reicht aber nicht, es muss ein unique key sein.
Wenn du z.B. 40 Produkte für 10€ hast, aber nur 20 Produkte pro Seite anzeigst, würdest du auf der nächsten Seite den Query mit products_price > 10€ haben und somit 20 Produkte nie angezeigt bekommen.