Optimierungsproblem

[devil13]

Guten Tag zusammen

nachdem mir in meiner letzten Frage in Punkto Datenbankorganisation geholfen werden konnte, habe ich nun eine neue Frage oder viel mehr ein Problem.

Ich habe nun alles so hinbekommen, dass es so funktioniert, wie ich es mir vorstelle. Jedoch scheint meine Variante etwas schlecht optimiert zu sein. Ich habe extrem lange suchzeiten und darum würde ich mich freuen, wenn ihr mir zeigen könntet, wie ich was optimieren kann.

Wer sich noch erinnert in meinem letzten Beispiel ging es um Wetterstationen und Temperaturmessungen. Dieses Beispiel möchte ich etwas weiterspinnen


Organisation:
1. <stationen>:
- id und name der Wetterstation, sowie letztes Änderungsdatum, aktuelle Temperatur und Temp. vor 5min, 1h, 1D, 1M, 1Y
2. <messungen> :
- id, stations id, Datum und Messwert zum Datum

CREATE TABLE IF NOT EXISTS stationen (id INT NOT NULL AUTO_INCREMENT UNIQUE, name VARCHAR(50) UNIQUE, t TIMESTAMP NULL, v FLOAT, v5min FLOAT, v1h FLOAT, v1d FLOAT, v1m FLOAT, v1y FLOAT);
CREATE TABLE IF NOT EXISTS messungen (id INT NOT NULL AUTO_INCREMENT UNIQUE, station INT, t TIMESTAMP, v FLOAT);

Abfragen:
- die Angaben in <stationen> sollen vor jeder Abfrage erst aktualisiert werden

Aktualisierung
(GetTimestamp() ist eine Funktion, die ich selbst geschrieben habe. Sie liefert einen Timestamp ... GetTimestamp(5min) gibt den Timestamp von NOW() - 5min zurück ... habe die Funktion hinreichend getestet)

UPDATE IGNORE stationen, (SELECT * FROM (SELECT * FROM messungen ORDER BY t DESC) AS messungen GROUP BY station) AS messungen SET stationen.v=messungen.v,stationen.t=messungen.t WHERE stationen.id=messungen.station;

UPDATE IGNORE stationen, (SELECT * FROM (SELECT * FROM messungen WHERE t <= GetTimestamp('5min') ORDER BY t DESC) AS messungen GROUP BY station) AS messungen SET stationen.v5min=messungen.v WHERE stationen.id=messungen.station;
UPDATE IGNORE stationen, (SELECT * FROM (SELECT * FROM messungen WHERE t <= GetTimestamp('1h') ORDER BY t DESC) AS messungen GROUP BY station) AS messungen SET stationen.v1h=messungen.v WHERE stationen.id=messungen.station;
UPDATE IGNORE stationen, (SELECT * FROM (SELECT * FROM messungen WHERE t <= GetTimestamp('1d') ORDER BY t DESC) AS messungen GROUP BY station) AS messungen SET stationen.v1d=messungen.v WHERE stationen.id=messungen.station;
UPDATE IGNORE stationen, (SELECT * FROM (SELECT * FROM messungen WHERE t <= GetTimestamp('1m') ORDER BY t DESC) AS messungen GROUP BY station) AS messungen SET stationen.v1m=messungen.v WHERE stationen.id=messungen.station;
UPDATE IGNORE stationen, (SELECT * FROM (SELECT * FROM messungen WHERE t <= GetTimestamp('1y') ORDER BY t DESC) AS messungen GROUP BY station) AS messungen SET stationen.v1y=messungen.v WHERE stationen.id=messungen.station;

Hinzufügen
(Auch hier habe ich mir Hilfsfunktionen gebaut. Mit der Funktion Fill erzeuge ich 300.000 Einträge in meiner Datenbank)

DROP PROCEDURE IF EXISTS AddMessungByTimestamp;
DELIMITER $$
CREATE PROCEDURE AddMessungByTimestamp(station_name VARCHAR(50), station_time TIMESTAMP, station_value FLOAT)
BEGIN
	INSERT IGNORE INTO stationen (name) VALUES(station_name);
	INSERT INTO messungen (station, t, v) (SELECT id, station_time, station_value FROM stationen WHERE name=station_name);
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS AddMessungByInt;
DELIMITER $$
CREATE PROCEDURE AddMessungByInt(station_name VARCHAR(50), station_time INT, station_value FLOAT)
BEGIN
	CALL AddMessungByTimestamp(station_name, FROM_UNIXTIME(station_time), station_value);
END $$
DELIMITER ;

DROP PROCEDURE IF EXISTS Fill;
DELIMITER $$
CREATE PROCEDURE Fill()
BEGIN
	SET @i = 100000;
	WHILE (@i > 0) DO
		CALL AddMessungByInt('potsdam', UNIX_TIMESTAMP() - @i, FLOOR(1 + (RAND() * 1000))/10);
		CALL AddMessungByInt('berlin', UNIX_TIMESTAMP() - @i, FLOOR(1 + (RAND() * 1000))/10);
		CALL AddMessungByInt('brandenburg', UNIX_TIMESTAMP() - @i, FLOOR(1 + (RAND() * 1000))/10);
		SET @i = @i - 1;
	END WHILE;	
END $$
DELIMITER ;

Problematik
1.) im Folgenden habe ich versucht die ältesten Daten einer Wetterstation zu finden.

SELECT * FROM (SELECT * FROM messungen ORDER BY t ASC) AS messungen GROUP BY station;

Funktioniert soweit auch wunderbar, dauert jedoch 0,3 Sekunden, was mir zu lange ist, denn

#SELECT * FROM messungen WHERE station = 1 ORDER BY t ASC LIMIT 1;

benötigt nur einen Bruchteil der Zeit.

2.) Ich denke aber, dass der Hauptangriffspunkt für eine Optimierung der Updatebereich ist, da ein Update von <stationen> sage und schreibe rund 64 Sekunden dauert, was mir absolut zu viel erscheint.

LG

[ChrisB]

EXPLAIN verwenden;
sinnvoll Indexe setzen.

[devil13]

EXPLAIN verwenden;
sinnvoll Indexe setzen.

Ja ... das Problem ist, dass ich weder weiß, wie ich 'sinnvoll' Indexe setze, noch was ich mit der Ausgabe von EXPLAIN anfangen soll.

EXPLAIN (SELECT * FROM (SELECT * FROM messungen WHERE t <= GetTimestamp('5min') ORDER BY t DESC) AS messungen GROUP BY station);

liefert z.b.


LG

[ChrisB]

Ja ... das Problem ist, dass ich weder weiß, wie ich 'sinnvoll' Indexe setze,

Wie/wo hast du versucht dich zu informieren, was ist dabei herausgekommen?

noch was ich mit der Ausgabe von EXPLAIN anfangen soll.

Wenn dort keine possible keys und kein tatsächlich verwendeter key vorkommen, ist das schlecht.
Und using temporary bzw. filesort willst du möglichst auch eliminieren, weil solche Dinge extrem Zeit kosten.

[devil13]

Wie/wo hast du versucht dich zu informieren, was ist dabei herausgekommen?

Ich lese das Buch 'Praktischer Einstieg in MYSQL mit PHP' von Sascha Kersken.
Jedoch verstehe ich bei weitem nicht alles, was dort geschrieben steht.

Außerdem habe ich versucht mich hier durchs Forum zu stöbern.

... und was dabei rausgekommen ist, habe ich ja oben eindrucksvoll gezeigt^^

Wenn dort keine possible keys und kein tatsächlich verwendeter key vorkommen, ist das schlecht.
Und using temporary bzw. filesort willst du möglichst auch eliminieren, weil solche Dinge extrem Zeit kosten.

Erneut die Frage nach dem Wie?!?

LG

[ChrisB]

Ich lese das Buch 'Praktischer Einstieg in MYSQL mit PHP' von Sascha Kersken.
Jedoch verstehe ich bei weitem nicht alles, was dort geschrieben steht.

Dann musst du es öfter/aufmerksamer/gründlicher lesen ... oder dir andere Quellen suchen.

Sowohl das Handbuch hat Hinweise zur Optimierung von Queries, als auch zahlreiche Blogs, etc.

Erneut die Frage nach dem Wie?!?

Erneut die Antwort - in dem du sinnvoll Indexe setzt.

Die Spalten, auf die sich deine WHERE- und ORDER-BY-Klauseln beziehen, sind erst mal naheliegendsten Kandidaten.