Optimierung komplexer mySQL-Query

D

DaSoMa

Mitglied
Hallo,

ich würde gerne eine recht komplexe Query in mySQL optimieren. Ich versuche mal das Problem zu schildern.

Ich bin gezwungen eine Datenbankstruktur für Messwerte zu verwenden, in der es eine Tabelle "datavalues" gibt. Dort werden bestimme Messwerte von User abgespeichert:
datavalueID, datavalue, LocalDateTime, SiteID, VariableID, MethodID, SourceID

Als Beispiel für den Datensatz:

1, 22, 2013-12-23 00:00:00, 2, 3, 60, 1
2, 25, 2013-12-23 00:05:00, 2, 3, 60, 1
3, 23, 2013-12-23 00:10:00, 2, 3, 60, 1
4, 26, 2013-12-23 00:00:00, 3, 3, 60, 1
5, 82, 2013-12-23 00:05:00, 3, 3, 60, 1
6, 25, 2013-12-23 00:10:00, 3, 3, 60, 1
7, 96, 2013-12-23 00:15:00, 2, 3, 60, 1
8, 42, 2013-12-23 00:20:00, 2, 3, 60, 1

Nun will ich daraus sozusagen eine bestimmte Struktur nachbilden, um das ganze später via PHP als CSV-Datei (mittels fputcsv) exportieren zu können. Diese muss dann so aussehen (relevant sind nur Zeit und Datavalue):

Zeit, DataVaklue ID 2, DataValue ID 3
2013-12-23 00:00:00,22,26
2013-12-23 00:05:00,25,82
2013-12-23 00:10:00,23,25
2013-12-23 00:15:00,96,
2013-12-23 00:20:00,42,

Das ganze is kein Problem für kleine Datenmengen - dauert aber je mehr SiteID im Datensatz vorhanden sein müssen und je größer der Zeitraum ist. Meine Query dazu schaut folgendermaßen aus:
CODE
SELECT DISTINCT
   dv.LocalDateTime,
   nr1site.DataValue as dv_nr1site,
   nr2site.DataValue as dv_nr2site
 FROM
   datavalues dv
 INNER JOIN datavalues nr1site ON nr1site.LocalDateTime=dv.LocalDateTime
 INNER JOIN datavalues nr2site ON nr2site.LocalDateTime=dv.LocalDateTime
 WHERE
   (dv.LocalDateTime BETWEEN '2013-10-09 22:00:00' AND '2014-01-29 04:38:00')
 AND
   (dv.SiteID=2  OR dv.SiteID=3)
 AND
   dv.VariableID='3'
 AND
   dv.MethodID='60'
 AND
   dv.SourceID='1'
 ORDER BY
   dv.LocalDateTime
 ASC



Die Ausführungszeit dieser Query mit meinem Datensatz (400000 Zeilen) liegt bei 15 Sekunden.
Unique Index über MethodID, LocalDateTime, VariableID, SiteID, SourceID ist gesetzt! Das Ergebnis ergibt 71000 Zeilen, da noch mehr SiteIDs vorhanden sind, als im Beispiel oben gezeigt.

Hat jemand Ideen um das ganze zu optimieren bzw. um an das Ergebnis wesentlich schneller zu kommen?

Vielen Dank,

DaSoMa
 
Ich glaube meine Query ist auch falsch - jetzt sehe ich, dass es nicht hinhaut. Wenn jemand eine bessere Variante hat, um ans Ziel zu kommen - ich höre gerne zu!
 
quick'n dirty

eine einfache abfrage formulieren:
CODE
SELECT ... FROM
datavalues
WHERE
...
ORDER BY
LocalDateTime
ASC



danach ein php-array aufbauen, ala:

CODE
$arr['2013-12-23 00:00:00'][] = $row['value'];



die arrays zeilenweise implodieren und mit dem key wegschreiben
 
Danke für die Antwort - die Lösung wollte ich als letztes Mittel nehmen. Ich hätte gedacht, dass da eine ordentliche Query machbar ist.
 
Verstehe ich das richtig, dass Du als Ergebnis eine Liste mit Werten pro Datum erhalten willst und dass es pro Datum u.U. unterschiedliche viele Werte sein können? Letzteres wirst Du mit einem SQL nicht hinkriegen. Außer die Zahl der unterschiedlichen Werte ist auf wenige begrenzt, so dass Du für jeden Wert ein JOIN einfügst. Dein SELECT sieht danach aus, dass dies so ist. Dann ist m.E. Deine Query schon ganz passend, außer dass Du aus dem INNER JOIN ein LEFT JOIN machen müsstest, damit Du auch solche Tage erhältst, bei denen nur einer der beiden Werte vorhanden ist.
Hast Du den Index als einen zusammengesetzten Index über alle Spalten angelegt oder auf jeder Spalte einen Index? M.E. müsste Ersteres besser sein.
Es würde mich übrigens nicht wundern, wenn das Sortieren (ORDER BY) den größten Teil der Ausführungsdauer in Anspruch nimmt.

HTH
Alex
 
Moin,

ich hab gestern auf Arbeit die erste Variante, die vorgeschlagen wurde, umgesetzt und die bringt schon beträchtlichen Zuwachs an Performance.
Ich werde es später nochmal mit LEFT JOIN versuchen, wie von alexr vorgeschlagen.

Der Index ist über alle Spalten die im WHERE zu sehen sind.

Die Geschichte mit der Sortierung hat mir auch schon Kopf zerbrechen gemacht... muss ich mal schauen, was mysql.com dazu sagt...

Und gespannt bin ich da, wie sich das ganze entwickeln wird, wenn nicht nur insgesamt 300000 Zeilen in der Tabelle vorhanden sind, sondern 20 bis 30 Millionen (wie es am Ende des Projektes sein wird)
 
letzten Endes ist es auch ein Frage, ob Du die richtige Hardware und Software hast, um mit solchen Datenbeständen zu arbeiten.

30 Millionen Datensätze würden mich eher an Oracle auf einem Sun Server denken lassen als an MySQL auf einem Webserver.
 
Leider kommt Sun und Oracle nicht in Frage, weil unbedingt eine Website gebraucht wird!
Ich schaue mal, wie das ganze in Zukunft performaen wird!
 
idee: lade doch die Daten täglich auf einen starken Rechner runter.
 
Ich haue nochmal kurz meine neue Query rein:

CODE
SELECT DISTINCT
dv.LocalDateTime,
       first.DataValue as first,
       second.DataValue as second
FROM
datavalues dv
LEFT JOIN datavalues first ON dv.LocalDateTime=first.LocalDateTime
LEFT JOIN datavalues second ON dv.LocalDateTime=second.LocalDateTime
WHERE
(dv.LocalDateTime BETWEEN '2014-01-01 00:00:00' AND '2014-04-01 00:00:00')
AND
dv.VariableID=3
AND
dv.MethodID=60
AND
(dv.SiteID=26 OR dv.SiteID=27)
ORDER BY dv.LocalDateTime ASC



Das Ergebnis ist FAST richtig:
2014-01-12 00:30:00,0,0
2014-01-12 00:35:00,0,0
2014-01-12 00:40:00, 0.400146484,0.400146484
2014-01-12 00:40:00,0.400146484,0
2014-01-12 00:40:00,0,0.400146484
2014-01-12 00:40:00, 0,0

Ab 00:40:00 wirds nun falsch. Zu erwarten wäre:
2014-01-12 00:30:00,0,0
2014-01-12 00:35:00,0,0
2014-01-12 00:40:00, 0.400146484,0

Aber da werden Quasi allo möglichen Kombinationen zu der Urzeit ausgespuckt.

EDIT/UPDATE:

Ich hab den Fehler wohl nun gefunden, mit der neuen Query schaut das Ergebnis richtig aus:

CODE SELECT DISTINCT
dv.LocalDateTime,
first.DataValue as first,
second.DataValue as second
FROM
datavalues dv
LEFT JOIN datavalues first ON (dv.LocalDateTime=first.LocalDateTime AND first.SiteID=26)
LEFT JOIN datavalues second ON (dv.LocalDateTime=second.LocalDateTime AND second.SiteID=27)
WHERE
(dv.LocalDateTime BETWEEN '2014-01-01 00:00:00' AND '2014-04-01 00:00:00')
AND
dv.VariableID=3
AND
dv.MethodID=60
AND
(dv.SiteID=26 OR dv.SiteID=27)
ORDER BY dv.LocalDateTime ASC
 
Brauchst Du wirklich das DISTINCT? Es wegzulassen könnte einiges an Geschwindigkeit bringen.

HTH
Alex
 
Sie müssen sich einloggen oder registrieren, um hier antworten zu können.
Zurück
Oben