Ich hab eine MySQL DB auf einem Embedded PC (Sheevaplug) laufen.
Dort existiert folgende Tabellenstruktur:
data:
id
timestamp (als unixtime)
entity-id
data
In der Tabelle sind aktuell ca. 7.2 Mio Einträge.
Was ich nun möchte ist folgendes:
Alle Werte einer bestimmten Entity, welche den Timestamp “am 01. eines Monats um 00:00 Uhr” tragen.
Meine Abfrage würde nun wie folgt aussehen:
SELECT
id,
DATE_FORMAT(from_unixtime(timestamp),"%Y-%m-%d %H:%i:%S") AS mydate,
data
FROM data
WHERE
entity = 12 AND
DATE_FORMAT(from_unixtime(timestamp),"%d %H:%i:%S") LIKE '01 00:00:00'
ORDER BY id ASC
Nun, das funktioniert. Dauer auf der kleinen Kiste aber unverhältnismäßig lange (ca. 90sek). Nehme ich die Datumsbedingung raus, so ist das ganze wieder wahnsinnig schnell. Aber dann hab ich wieder alle Werte …
Hier noch ein Screenshot der Tabellen-Eigenschaften:
Kann man da (Tabelleneinstellung, Abfrage, …) noch etwas optimieren ohne gleich die Hardware zu tauschen?
Gruß
Alex
P.S. Ich weiß dass dieses Board ein Unterforum des Java-Boards ist. Java hab ich hier nicht mit im Spiel. Die Abfrage wird über PHP getriggert. Aber es geht ja auch weniger um die Sprache als um die Abfrage und die Tabelle selbst, was ja auch Bestandteil dieses Unterforums ist.
Das Problem sehe ich hier erst mal darin, dass du den Time-Wert erst in einen String formatierst und dann abfragst. Solche String-Vergleiche sind natürlich quälend langsam.
Mit MySQL arbeite ich beruflich leider nicht, aber versuche mal mit den Date-Functions: https://maheshkanna.wordpress.com/2012/11/26/mysql-date-functions/
Ein [SQL]WHERE MINUTE(FROM_UNIXTIME(timestamp)) = 0 AND HOUR(FROM_UNIXTIME(timestamp)) = 0 AND DAYOFMONTH(FROM_UNIXTIME(timestamp)) = 1[/SQL] müsste da performanter sein. Ich bin mir nur nicht sicher wie “teuer” es auf MySql ist, den Timestamp erst in ein Date zu wandeln. (Code ohne Gewähr, nicht getestet. So ähnlich halt ;))
Danke für den Tipp (auch mit dem separaten Unterforum). Aber wirklich schneller ist das noch nicht. Dauert in der Konstellation ebenfalls ca. 90sek.
[update]
Mir kommt gerade eine andere Idee:
Die Datenbank umfasst keine 100 Jahre an Daten. Somit wäre es wohl möglich zu schauen “Von wann ist der erste Eintrag”, und dann die “Arbeit” die sonst MySQL erledigt, in das SELECT Statement zu verfrachten:
SELECT … FROM … WHERE timestamp in (x, y, z, …)
Wobei x, y, z und … zuvor gewählte unix timestamps, zwischen dem ersten vorhanden Eintrag und jetzt sind, jeweils Monatsanfang 00:00 …
Hab das mal exemplarisch mit 2 Monaten getestet. Geht wie erwartet rapide schnell. Da ich bis dato nur knapp 2 Jahre an Daten habe, und eigentlich auch nur die letzten 2 Jahre interessieren, geht muss ich vor dem Ausführen des Statements nur 24 timestamps ausrechnen und in das Statement einfügen… Aber ich denke selbst mit 10 Jahren (120 Timestamps) sollte das noch schnell genug gehen.
Schade, aber damit wäre geklärt wie teuer die Umwandlung ist. Die Möglichkeit die Tabelle zu erweitern hast du wahrscheinlich nicht, oder?
Macht es einen Unterschied wenn du (zum Testen) das DATE_FORMAT aus der Feldliste rauslässt?
Könnte die Tabelle schon ändern/erweitern und das ganze als DATETIME speichern, womit ich mir diese Konvertireung spare und dann wieder auf String-Ebene (oder so) vergleiche. Das wäre auch noch eine Idee. Aber so ist es glaub ich immer noch am schnellsten, da auf MySQL Seite gar nichts mehr konvertiert werden muss, und direkt über den Index gegangen werden kann.
Ohne DATE_FORMAT im SELECT Teil dauert die Abfrage 95sek. Aber das kann jetzt auch eine anderweitige CPU Belastung ausgemacht haben.
Das heisst, du berechnest jetzt die Timestamps quasi vor (das Edit hatte ich gar nicht mehr gesehen)? So lange MySql die Anzahl an Parametern in der Bedingung frisst, spricht da eigentlich, rein praktisch gesehen, nichts dagegen.
Dann könnte es höchstens noch helfen einen Index auf die Column mit den Timestamps zu setzen.
Edit: MySql Handbuch:
„The number of values in the IN list is only limited by the max_allowed_packet value“
Das sollte in deinem Fall für ein paartausend Jahre reichen…
Jepp. Es geht hier um die monatlichen Werte einer Heizungs- und Photovoltaik-Anlage. Da interessiert eigentlich nur die letzten 2 Jahre. Zur Not auch mal die letzten 10 Jahre. Und wenn’s ganz übel kommt die Daten zwischen “vor 10 Jahren” und “vor 5 Jahren”.
Also summa summarum irgendwas um max. 5-10 Jahre. Macht im Worst-Case 120 Timestamps die ich zuvor “berechnen” und in der Where-Bedingung aufführen muss.
Einen Index auf die Timestamps hab ich schon. Die Abfrage dauert jetzt bei “den letzten 8 Monaten” irgendwas im Millisekundenbereich.
*** Edit ***
Dass ich da nicht schon früher drauf gekommen bin… hammer An einer ganz anderen Stelle bringt mir dieses Vorgehen massive Geschwindigkeitsvorteile:
Über ein HTML-Formular kann der Benutzer auswählen welche Entitäten er für welchen Zeitraum visualisiert haben möchte. Da die Daten im Minutentakt aufgezeichnet werden, kommt da eben ganz schön was zusammen. Bisher war es am einfachsten den ganzen Zeitraum abzufragen, und dann vor der visualisierung “auszusortieren” und auf eine “vernünftige” Datengröße (z.B. generell nur 300 Messwerte gleichmäßig verteilt auf den ausgewählten Zeitraum) zu reduzieren, sprich: Messwerte im Abfrageergebnis einfach überspringen.
Mit dem generieren der Timestamps VOR der Abfrage reduziert sich natürlich auch die Anzahl der Ergebniszeilen. Aussortieren muss ich auch nicht mehr.
Sogar mit 500 Timestamps im SELECT geht das ratz-fatz.
Ich will jetzt nicht sagen dass das ein Allheilmittel ist. Nein. Aber für langsame Kisten mit vielen Daten und einer so “einfachen” Tabellenstruktur, ergibt dieses Vorgehen einen ganz schönen Geschwindigkeitsvorteil.
Warum verwendest du nicht TIMESTAMP als Datentypen für den Timestamp? Damit kann MySQL deutlich mehr anfangen - das wird in dem Buch “High Performance MySQL” übrigens auch empfohlen.
Außerdem solltest du dir deine Query mal mit EXPLAIN erklären lassen. Dort siehst du dann ggf. auch den Performancekiller.