Eine SQL-Abfrage umformulieren

[Molaf]

Hallo allerseits,

ich hänge grade an der korrekten Syntax einer Abfrage fest, und bin mir nicht mal mehr sicher, ob es mit einer einzelnen Abfrage überhaupt geht.

Eine Beschreibung der Datenbank:

Die Daten sind etwa 780.000 Datensätze, die relevanten Keys sind:
id : Primary Key mit AUTOINC
timest : Unixsystemzeit (s) von Script das die Daten eingesammelt hat
maschi : Kürzel einer Maschine, von der die Daten stammen

Es sind derzeit Daten von 26 Maschinen in der DB und ich brauche ab und zu die jeweils letzten Datensätze jeder Maschine mit den höchsten Timestamps.
Die habe ich bisher so abgefragt:

SELECT *, MAX(timest) AS timest FROM daten GROUP BY maschi

Die Abfrage dauert fast 15 Sekunden und ist damit unerträglich langsam, trotzdem liefert sie schonmal das richtige Ergebnis.

Nun habe ich noch einige andere Kombinationen ausprobiert, halbwegs erfolgreich war:

SELECT * FROM daten GROUP BY maschi ORDER BY `timest` DESC LIMIT 1

(Liefert den Treffer für eine Maschine, dauert nur 2.9 sec)

Wenn ich nur für die Abfrage mit einem WHERE auf eine Maschine reduziere, muss ich trotzdem 26 Abfragen abschicken, was im Ergebnis noch langsamer wäre.

Irgendwie muss es doch auch anders gehen.

Kann ich meine erste Abfrage irgendwie optimieren oder umformulieren, sodass ich eine Abfrage in weniger als 5 sec hinbekomme?

SELECT * FROM daten GROUP BY maschi ORDER BY `timest` DESC LIMIT 26

(Liefert zwar für jede Maschine einen Datensatz, aber nur noch einer davon stimmt auch. Die Zeit für die Abfrage bleibt allerdings bei etwa 2.9 sec stabil)

Gruß,
Molaf

[jan61]

Moin,

erstmal ist ein * in Verbindung mit einer Aggregatfunktion keine gute Idee - nach der reinen SQL-Lehre muss man eigentlich alle Spalten, die nicht in Aggregatfunktionen auftauchen, in die GROUP BY-Klausel aufnehmen.

Die Geschwindigkeit der Abfrage hängt vorrangig davon ab, wie die Tabelle indiziert ist. Liegt auf timest ein Index?

Jan

[Sid Burn]

Welches DBMS nutzt du den?

Wenn es MySQL ist dann musst du noch beachten das pro Tabelle immer nur ein index genutzt werden kann. Daher wenn du zwei Felder "maschi" und "timest" abfragst, solltest du einen index anlegen der beide felder in einem index enthält. Ansonsten würde MySQL nur ein index nutzen auch wenn du auf beide Felder ein index gesetzt hast.

[Molaf]

Entschuldigung erstmal, dass ich mich so lange nicht gemeldet habe, ich war krankheitsbedingt zwei Wochen ans Bett gefesselt.

Die Spalte 'timest' ist nicht Teil des primary keys. Wenn ich recht sehe ist das der einzigste index den man bei MySQL hat.

Morgen habe ich wieder Zugriff auf eine Testdatenbank gleicher Grösse, und die werde ich mal umgestalten auf zwei prmary keys.
Ich habe mir aber auch schon gedacht, ob ich bei den Aktualisierungen der Daten (es werden nur neue hinzugefügt, niemals gelöscht) nicht einfach je Maschine die neusten Zustände als YAML speichere abseits der DB.
Damit erspare ich für jede Anzeige der aktuellen Daten eine komlexe Suche nach den simplen Daten. Vielleicht ist das der unelegantere aber bessere Weg, ich werde beides mal testen.

Grüsse,
Molaf

[jan61]

Moin,

schaust Du hier: http://dev.mysql.com/doc/refman/5.1/...ate-index.html - MySQL kann durchaus auch Indizes abseits des Primary Key erstellen. Sooo schlecht is die DB ja nun auch nicht ;-)

Jan

[Molaf]

Neuste Meldungen:

Ich habe mal die Spalte 'timest' in den Index aufgenommen.
Danach läuft die Abfrage ganze 0.2 Sekunden schneller ab.
Dann habe ich eben mal probeweise noch 'maschi' mit in den Index aufgenommen, das hat keinen messbaren Einfluss mehr gehabt.

Vielleicht sind es einfach zu viele Datensätze.

Dann habe ich mal das Ergebnis der Abfrage als YAML gespeichert und aktualisiere die einzelnen Werte beim Einsammeln neuer Daten gleich mit.
Das Einlesen und Parsen des YAML braucht insgesamt nur 0.16.. s .
Damit ist mein Problem resourcenschondend gelöst. Schade nur, dass ich trotzdem nicht sagen kann, was genau bei der DB so stark gebremst hat.

@jan61: Ich behaupte ja auch nicht dass die DB sooo schlecht ist, im Zweifelsfall liegt es sicher an mir, wenn etwas nicht hinhaut. Das mit dem Index war mir vorher z.B. noch gar nicht bekannt.

[jan61]

Moin,

Ich habe mal die Spalte 'timest' in den Index aufgenommen.
Danach läuft die Abfrage ganze 0.2 Sekunden schneller ab.
Dann habe ich eben mal probeweise noch 'maschi' mit in den Index aufgenommen, das hat keinen messbaren Einfluss mehr gehabt.

Vielleicht sind es einfach zu viele Datensätze.

Naja, 780.000 Sätze sind ja eigentlich noch normales Futter für eine DB. Könnte höchstens sein, dass die HW überfordert ist.

Du kannst aber (mit den bestehenden Indizes) die Abfrage mal so starten und dann gucken, was rauskommt:

SELECT maschi, MAX(timest) AS timest FROM daten GROUP BY maschi

@jan61: Ich behaupte ja auch nicht dass die DB sooo schlecht ist...

Nein, aber ich tue das gern ;-) Ich bin kein großer MySQL-Fan - aber das ist eine andere Geschichte.

Jan

EDIT: Noch 2 Infos, wenn Du Interesse hast, hinter das Problem zu kommen:
1. Nach dem Hinzufügen eines Index solltest Du einen "analyze table [tabelle]" gefahren haben, damit kann die DB-Engine ihre Informationen über die Statistiken der Tabelle aktualisieren und in die Berechnung der Query-Kosten einbeziehen. Das ist eigentlich immer dann nützlich, wenn sich an der Struktur oder der Anzahl der Datensätze signifikante Änderungen ergeben (das gilt übrigens für fast jede DB).
2. Mehr zum Thema Query-Optimierung findest Du hier: http://dev.mysql.com/doc/refman/5.1/en/query-speed.html