Abfrage über zwei Tabellen, sort, LIMIT 10, Abfrage dauert 45 Sekunden

[Pavel]

Hallo in die Runde, Ich habe eine Datenbank. In einer Tabelle (abrufe) sind ca. 200000 Datensätze. Ich möchte mir die letzten 10 Ausgeben lassen, es geht aber es dauert 45 Sekunden, was ja nicht sein sollte.

Wie kann ich die Abfrage so gestalten, dass sie vielleicht erst die letzten 10 ausliest anstatt alle um dann die letzten 10 herzunehmen.

SELECT a.*, b.gemeinde FROM abrufe a, plz b WHERE a.plz=b.plz ORDER BY a.datum DESC LIMIT 10 

[ursus contionabundo]

Ich habe eine Datenbank.

Das ist eine nützliche Information. Die "Datenbanken" optimieren solche Abfragen nämlich unterschiedlich.

SELECT a.*, b.gemeinde FROM abrufe a, plz b WHERE a.plz=b.plz ORDER BY a.datum DESC LIMIT 10

Lege einen Index auf abrufe.plz, gemeinde.plz, abrufe.datum.

Und sieh Dir bei solchen Problemen mit Abfragen immer die Ausgaben von explain an:

EXPLAIN SELECT a.*, b.gemeinde FROM abrufe a, plz b WHERE a.plz=b.plz ORDER BY a.datum DESC LIMIT 10;

[dedlfix]

Tach!

Wie kann ich die Abfrage so gestalten, dass sie vielleicht erst die letzten 10 ausliest anstatt alle um dann die letzten 10 herzunehmen.

SELECT a.*, b.gemeinde FROM abrufe a, plz b WHERE a.plz=b.plz ORDER BY a.datum DESC LIMIT 10 

Das ist keine Frage der Abfrageformulierung in dem Fall, denn die ist bereits optimal. Vielmehr lässt sich an der Abfrage nichts verbessern, denn wenn du die letzten (oder auch ersten) x Datensätze haben möchtest, muss die Tabelle erstmal sortiert werden, damit es überhaupt feststellen kann, welche Datensätze es sein sollen. Wenn die Datenmenge nun im unsortierten Zustand vorliegt, dauert das dann eben. Um die Datenmenge gleich sortiert vorliegen zu haben, setzt man einen Index auf die betroffene Spalte, hier also das Datum.

dedlfix.

[Rolf B]

Hallo dedlfix,

die Frage ist, was der Aufwandstreiber ist. Ist es der Join über die PLZ, oder der ORDER BY der Ergebnismenge. Findet ein Filesort statt, oder ein indexed read?

Je nach dem, wie dumm MYSQL vorgeht, erzeugt er zuerst den Join über alle Sätze, sortiert dann und führt den LIMIT durch. Was hier helfen kann, ist das Vorziehen des LIMIT. Ich habe allerdings gerade kein MYSQL laufen und bin nicht sicher ob ein innerer LIMIT zulässig ist...

SELECT ab.*, b.gemeinde
FROM (SELECT * FROM abrufe ORDER BY a.datum DESC LIMIT 10) ab,
     plz b
WHERE ab.plz = b.plz

Alternativ kann auch die Dedlfixsche Standardtransformation von Joins in Subselects helfen:

SELECT a.*, (SELECT gemeinde FROM plz b WHERE b.plz=a.plz)
FROM abrufe 
ORDER BY a.datum DESC LIMIT 10

Welcher von beiden besser ist, muss ein Explain ergeben. Ein Index auf die PLZ-Spalte in der PLZ Tabelle und auf die DATUM-Spalte der Abrufe-Tabelle dürfte aber auf jeden Fall hilfreich sein.

Rolf