MySQL Query mit Group und JOIN optimieren. (gelöst) (sonstige Sprachen, auch PHP)

[thread]20230[/thread]

MySQL Query mit Group und JOIN optimieren. [gelöst]

Leser: 12

Articles: hide open all | hide show old branches

+16 replies
kami

2017-01-26 13:30
User since
2010-03-17
92 Artikel
BenutzerIn
Hallo zusammen,

ich habe 2 große Tabellen (100.000 Einträge) mit unterschiedlichen nicht synchronen Zeitstempeln von Temperaturmessungen. Ich möchte nur gerne aus beiden Tabellen mir Temperaturwerte herausnehmen und in einem Query zusammenlegen. Dafür habe ich folgendes Query, was aber viel zu langsam ist. Es soll immer eine ganze Kalendarwoche ausgegeben werden. Wer kann das bitte optimieren.
Code: (dl )

1 2 3 4 5 6 7 8 9 10 11 12 13

SELECT UNIX_TIMESTAMP(l.TimeDate) time , AVG(l.intemp) , AVG(n.intemp) , DATE_FORMAT(l.TimeDate, '%Y-%m-%d-%H') dates FROM values.temps l LEFT JOIN values.net n ON DATE_FORMAT(l.TimeDate, '%Y-%m-%d-%H') = DATE_FORMAT(n.TimeDate, '%Y-%m-%d-%H') WHERE YEARWEEK('2017-01-17 00:00:00',1) = YEARWEEK(l.TimeDate,1) GROUP BY dates ORDER BY dates ASC
Vielen Dank.

Gruß kami
- +14 replies
- rosti
  
  2017-01-26 14:09
  
  User since
  2011-03-19
  3492 Artikel
  BenutzerIn
  
  Kannst Du selbst optimieren: explain kennst Du?
  
  Zeig mal die Indizierung Deiner Tabellen, show create...
  - +13 replies
  - kami
    
    2017-01-26 14:33
    User since
    2010-03-17
    92 Artikel
    BenutzerIn
    
    Hi,
    
    also l und n sehen so aus:
    
    Code: (dl )
    
    1 2 3 4 5 6 7
    
    CREATE TABLE `temps` ( `id` int(11) NOT NULL AUTO_INCREMENT, `TimeDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `in` float NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `id` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4599 DEFAULT CHARSET=utf8
    
    Explain kenne ich nicht und verschlanken wäre sehr gut :)
    
    Gruß kami
    - +12 replies
    - rosti
      
      2017-01-26 14:44
      
      User since
      2011-03-19
      3492 Artikel
      BenutzerIn
      
      Setz mal einen index auf TimeDate, sollte schon was bringen.
      
      explain: Einfach vor das Statement notieren, das erklärt Dir die Query.
      
      Den key 'id' kannst Du löschen, Du hast primary auf id.
      - +11 replies
      - kami
        
        2017-01-26 15:26
        
        User since
        2010-03-17
        92 Artikel
        BenutzerIn
        
        Hi,
        
        habe ich in beiden Datenbank gemacht. Hat aber nicht wirklich viel gebracht. Er braucht halt für die eine Woche fast 2 Minuten. Ist auch nur ein Raspberry auf dem das läuft. Deswegen wäre eine Optimierung des Queries echt sinnvoll :)
        
        Gruß kami
        
        +10 replies
        
        rosti
        
        2017-01-26 15:31
        
        User since
        2011-03-19
        3492 Artikel
        BenutzerIn
        
        Ein Index wirkt insbesondere in der where-Klause. Du kannst es Dir mit explain anschauen und damit das Optimieren verstehen. 100.000 Einträge sind nicht viel, normalerweise liegen Abfragen im Millisekundenbereich.
        
        Wenn nicht, machst Du was grob fahrlässig falsch ;)
        
        +9 replies
        
        Muffi
        
        2017-01-26 15:35
        
        User since
        2012-07-18
        1465 Artikel
        BenutzerIn
        
        Ja macht er, schrieb ich auch unten
        1 + 1 = 10
        
        +8 replies
        
        kami
        
        2017-01-26 15:41
        
        User since
        2010-03-17
        92 Artikel
        BenutzerIn
        
        Okay das habe ich verstanden. Ich bin aber gerade irgendwie "betriebsblind" wie würdet ihr das denn machen?
        
        Gruß kami
        
        +5 replies
        
        Muffi
        
        2017-01-26 15:46
        
        User since
        2012-07-18
        1465 Artikel
        BenutzerIn
        
        Ich hab noch nicht mal so ganz verstanden was die Query genau tun soll.
        Kannst du das in Worten beschreiben?
        
        edit: Ich würd irgendwo grob in die Richtung gehen:
        
        Code: (dl )
        
        ON n.TimeDate BETWEEN l.TimeDate - INTERVAL 1 HOUR AND l.TimeDate + INTERVAL 1 HOUR
        
        Last edited: 2017-01-26 16:05:30 +0100 (CET)
        1 + 1 = 10
        
        +4 replies
        
        Muffi
        
        2017-01-27 08:32
        
        User since
        2012-07-18
        1465 Artikel
        BenutzerIn
        
        Achja, wenn das dann funktioniert kann man auch den l.TimeDate im WHERE aus seiner Funktion befreien mit BETWEEN.
        Das sollt, wenn auch viel weniger als ein guter join, auch was bringen.
        1 + 1 = 10
        
        +3 replies
        
        kami
        
        2017-01-27 08:54
        
        User since
        2010-03-17
        92 Artikel
        BenutzerIn
        
        Hi,
        
        klar kann ich gerne nochmal erklären. Tabelle 1 und 2 haben unterschiedliche Zeitstempel aber nehmen minütlich Temperaturwerte auf. Einmal pro Woche wird daraus ein Diagramm erstellt mit den gesamten Temperaturverläufen der letzten Kalendarwoche. Dazu soll ein LEFT JOIN von 1 auf 2 gemacht werden, weil 1 die führende Tabelle ist.
        
        Okay? Das funktioniert mit dem Query ist aber sehr Ressourcen intensiv und die Abfrage dauert zur Zeit 2 min.
        
        Gruß kami
        
        Muffi
        
        2017-01-27 09:07
        
        User since
        2012-07-18
        1465 Artikel
        BenutzerIn
        
        Mhm, dann sollt das mit dem BETWEEN schon funktionieren.
        Musst halt vorher Start und Ende ausrechnen.
        
        Weiß nicht, ob man ein Datum auf Stunden Truncaten kann, ansonsten
        vielleicht sowas in der Art:
        
        Start:
        STR_TO_DATE(DATE_FORMAT(l.TimeDate, '%Y-%m-%d-%H'), '%Y-%m-%d-%H')
        Ende:
        STR_TO_DATE(DATE_FORMAT(l.TimeDate, '%Y-%m-%d-%H'), '%Y-%m-%d-%H') + INTERVAL 1 HOUR
        
        Und im WHERE mit der Woche dann ähnlich.
        
        PS: Nicht vergessen den Göttern der MySQL-Indexe was zu opfern, dass sie auch benutzt werden ;)
        Last edited: 2017-01-27 09:11:56 +0100 (CET)
        1 + 1 = 10
        
        Muffi
        
        2017-01-31 11:28
        
        User since
        2012-07-18
        1465 Artikel
        BenutzerIn
        
        Hats jetzt was gebracht?
        Wo liegst du jetzt zeitmäßig?
        
        Weil ne kleine Idee hab ich noch wie man noch was rausholen könnte.
        1 + 1 = 10
        
        +2 replies
        
        rosti
        
        2017-01-27 09:22
        
        User since
        2011-03-19
        3492 Artikel
        BenutzerIn
        
        2017-01-26T14:41:17 kami
        Okay das habe ich verstanden. Ich bin aber gerade irgendwie "betriebsblind" wie würdet ihr das denn machen?
        
        Gruß kami
        
        Lernen damit umzugehen. Das ist alles kein Hexenwerk von irgendwelchen Göttern, das kann jeder lernen. Tipps hast Du ja nun genug bekommen hier, jetzt ist es an der Zeit, dass Du Dich selbst mal bemühst.
        
        Viel Erfolg!
        
        kami
        
        2017-01-31 17:46
        
        User since
        2010-03-17
        92 Artikel
        BenutzerIn
        
        Hallo zusammen,
        
        also das Thema ist geschlossen. Ich habe mich dazu entschieden in meinem Python-Skript die Zusammenlegung zu machen. Damit bin ich in der Gesamtauswertung von 15-17 Minuten auf 1 Minute runtergekommen.
        
        Das reicht mir voll aus und ist schneller als ich durch ein Query jede gedachte hätte.
        
        Trotzdem vielen Dank.
        
        Gruß kami
- Muffi
  
  2017-01-26 14:23
  
  User since
  2012-07-18
  1465 Artikel
  BenutzerIn
  
  Mit Funktionsergebnissen zu joinen ist performancetechnisch ganz übel, weil er keine Indexe benutzen kann.
  
  edit: sollte hier auch nicht nötig sein
  Last edited: 2017-01-26 14:26:05 +0100 (CET)
  1 + 1 = 10

View all threads created 2017-01-26 13:30.