Join über drei Tabellen mit timestamp

[BlauesLicht]

Hallo Freunde,
Ich möchte gerne die Daten aus drei Tabellen (history, brenner, temperaturen) tageweise zusammenfassen.
Sprich: Ich möchte für jeden Tag die Durchschnittsaussentemperatur, die Brennerstarts meiner Therme und den Gasverbrauch darstellen.
Folgende Einzelquerys laufen einwandfrei und sind auch schnell:

SELECT timestamp, avg(getTempA) FROM temperaturen GROUP BY date(timestamp);
Index auf timestamp

SELECT timestamp, max(value)-min(value) FROM history WHERE READING='countsOverall' GROUP BY date(timestamp);
Index auf timestamp,reading,device

SELECT timestamp,max(getBrennerStarts)-min(getBrennerStarts) FROM brenner GROUP BY date(timestamp);
Index auf timestamp

Versuche ich nun die drei Abfragen über LEFT JOIN für den Monat November zu verbinden, läuft der query bis in alle Ewigkeit

select date(t1.timestamp) as Datum,avg(t1.getTempA) as DTemp , max(t2.getBrennerStarts)-min(t2.getBrennerStarts) as BStarts, (max(t3.value)-min(t3.value)) / 100  as Verbrauch from temperaturen as t1 left join brenner as t2 on date(t2.timestamp)=date(t1.timestamp) left join history as t3 on date(t3.timestamp)=date(t2.timestamp) where month(t1.timestamp)='11' and t3.READING='countsOverall' group by date(t1.timestamp);

Ich könnte mir das Nadelöhr bei date(t2.timestamp)=date(t1.timestamp) und date(t3.timestamp)=date(t2.timestamp) vorstellen, denn er muss ja alles erst konvertieren und dann vergleichen. Aber wie könnte man das anders lösen?

ein explain verät mir folgendes:

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |  31401 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |  31441 | Using where; Using join buffer               |
|  1 | SIMPLE      | t3    | ALL  | NULL          | NULL | NULL    | NULL | 320715 | Using where; Using join buffer               |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+

Soweit ich das verstanden habe, ist dass der denkbar schlechteste Fall, denn er sucht über alle Daten...

Beispieldaten:

mysql> SELECT timestamp,getBrennerStarts FROM brenner ORDER BY timestamp DESC LIMIT 5;
+---------------------+------------------+
| timestamp           | getBrennerStarts |
+---------------------+------------------+
| 2016-12-01 17:45:08 |           202795 |
| 2016-12-01 17:40:15 |           202795 |
| 2016-12-01 17:35:14 |           202794 |
| 2016-12-01 17:30:08 |           202794 |
| 2016-12-01 17:25:13 |           202794 |
+---------------------+------------------+

mysql> SELECT timestamp, value, READING  FROM history WHERE READING='countsOverall' ORDER BY timestamp DESC LIMIT 5;;
+---------------------+-----------+---------------+
| timestamp           | value     | READING       |
+---------------------+-----------+---------------+
| 2016-12-01 17:47:09 | 381844.00 | countsOverall |
| 2016-12-01 17:46:17 | 381843.00 | countsOverall |
| 2016-12-01 17:45:25 | 381842.00 | countsOverall |
| 2016-12-01 17:44:34 | 381841.00 | countsOverall |
| 2016-12-01 17:43:42 | 381840.00 | countsOverall |
+---------------------+-----------+---------------+

mysql>SELECT timestamp, getTempA FROM temperaturen ORDERBY timestamp DESCLIMIT5;
+---------------------+----------+
| timestamp          | getTempA |
+---------------------+----------+
|2016-12-01 17:45:08 |     6.3|
|2016-12-01 17:40:16|     6.2|
|2016-12-01 17:35:14|     6.3|
|2016-12-01 17:30:08 |     6.6|
|2016-12-01 17:25:14|     6.2|
+---------------------+----------+

 

[akretschmer]

Da sind mehrere Probleme:

• "SELECT timestamp, avg(getTempA) FROM temperaturen GROUP BY date(timestamp);" Das funktioniert zwar in MySQL, aber nur, weil hier ein Bug in MySQL ist. Die Abfrage ist logisch falsch, alle Spalten im Resultset müssen ntweder aggregiert oder gruppert sein. Das ist nicht der Fall.
• dein "t1 left join brenner as t2 on date(t2.timestamp)=date(t1.timestamp)" wird Dir aus allen timestamps eines Tages aus T1 mit alle timestamps aus t2 joinen, weil das date(...) ja immer dasselbe liefert (falls je Tag mehrere Timestamps sind). Damit machst Du hier eher sowas wie eine Cross Join (je Tag)
• für den Join kann kein Index genutzt werden, weil der Index max. auf timestamp ist, nicht aber auf date(timestamp). Funktionale Indexe kann MySQL (wie ganz viele andere Dinge) halt eben nicht.

Mach Deine Aggregation erst einmal in den Tabellen, selecte das Datum und gruppiere darüber, und joine dann auf das Datum. Das wird zwar noch imme rnicht sonderlich schnell sein, würde aber deutlich den Brechreiz beim Lesen der Selects reduzieren. Immerhin.