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 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