Probleme mit SQL Abfrage - bestimmte Anzahl an Messungen/pro Ort

[mexxwell33]

Hallo Leute,

Ich hoffe es kann mir jemand bei meinem SQL Problem weiterhelfen. Das verwendete DBMS ist MySQL 4.1.

In der DB ist eine Tabelle mit den folgenden Spalten (id, time, messpunkt, data, ....).
Die Tabelle wird von verschiedenen Messpunkten aus mit Daten befüllt. (Zeitpunkt der Messung, Name der Messstation, Daten, etc.) Dies geschieht pro Station ca. alle 1,5 Minuten.

Für diverse Auswertungen brauche ich ein performante Abfrage, die mir die letzten x Messungen jeder Messstation ausgibt.

Ich habe bereits versucht eine Abfrage mit Subselect und LIMIT zu basteln, jedoch wird LIMIT im Subselect bei MySQL 4.1 nicht unterstützt.

Ein weiterer Versuch war es mit Hilfe der Zeit auf das gewünschte Ergebnis zu kommen und den Rest in der Applikation auszufiltern.

SELECT id, time, messpunkt, data FROM messungen as l_t
      INNER JOIN (select SUBDATE(max(time), INTERVAL 15 MINUTE) as c_time,
                          messpunkt FROM messungen) as r_t
      ON l_t.messpunkt = r_t.messpunkt
WHERE  l_t.time > r_t.c_time
ORDER BY time DESC

Jedoch dauert die Abfrage auf Grund der großen Datenmenge eine kleine Ewigkeit, und bei Einschränkung auf z.B. nur Messungen der letzten Stunde kann es sein das Daten fehlen, da eine Station eventuell schon längere Zeit keine Informationen gesendet hat.<br/>

Ich hoffe auf ein paar Wertvolle Tipps :-)

[Frank (no reg)]

Hallo,

unterstützt MySQL überhaupt LIMIT in Subqueries? Wenn ja, dann könntest du dein Subselect innerhalb der WHERE Klausel machen, ansonsten sieht's wohl eher schlecht aus und du müsstest mit mehreren Abfragen arbeiten.

Wieviele unterschiedliche Mess-Stationen sind es denn? Vielleicht ist dann bei geeigneter Indizierung der Tabelle die einzelne Ausführung von 15 Abfragen zusammen schneller als eine riesige (welche intern erstmal Tonnen von Zwischendaten erzeugt) Abfrage mit Joins.

Ciao, Frank

[solkar]

Hallo!

Die Subquery

(SELECT SUBDATE(MAX(time), INTERVAL 15 MINUTE) AS c_time,
                          messpunkt AS m2 FROM messungen) AS r_t

sieht so aus als wolltest Du max(time)-15m PRO MESSPUNKT ermitteln; das tut sie aber nicht, sondern sie ermittelt max(time)-15m auf der ganzen Tabelle!
Die Subquery bringt also nicht viel aber JOINed eine grosse Tabelle mit sich selbst.

(MySQL 6.0.5 meckerte die Syntax an; deshalb hab ich "AS m2" ergänzt.)

---

Wichtig wäre es mal die Datentypen und Indices zu betrachten:

• Typ von messpunkt? Indiziert?
• Typ von data? Ist das ein Blob? Falls ja, wie groß im Mittel?

Insgesamt wäre natürlich das Mengengerüst wichtig:

• Aktuelle Zahl der Datensätze?
• Anzahl der Messpunkte?

Grüsse

Solkar

[mexxwell33]

Hallo!

Die Subquery

(SELECT SUBDATE(MAX(time), INTERVAL 15 MINUTE) AS c_time,
                          messpunkt AS m2 FROM messungen) AS r_t

sieht so aus als wolltest Du max(time)-15m PRO MESSPUNKT ermitteln; das tut sie aber nicht, sondern sie ermittelt max(time)-15m auf der ganzen Tabelle!

Sorry habe vergessen das group by mit ein zu tippen! Die Abfrage sieht wie folgt aus:

SELECT id, time, messpunkt, data FROM messungen as l_t
      INNER JOIN (select SUBDATE(max(time), INTERVAL 15 MINUTE) as c_time,
                          messpunkt FROM messungen
                  group by messpunkt ) as r_t
      ON l_t.messpunkt = r_t.messpunkt
WHERE  l_t.time > r_t.c_time
ORDER BY time DESC

Mit der Subquery erhalte ich den Zeitpunkt für die Messungen vor 15 Minuten, und den verwende ich in der äußeren Abfrage als Prüfung. (l_t.time > r_t.c_time)
Somit bekomme ich die letzten 10 Messungen aller Stationen.

Da es ca. 700 Messpunkte und daher eine Menge an Daten in der Tabelle vorhanden sind dauert die Abfrage aber ewig lange :-(

(Limit ist in MySql 4.1 Subqueries nicht unterstützt.....)

Ich versuche das Problem nun mal mit mehreren Abfragen zu lösen, wenn mir aber jemand einen Tipp geben könnte wäre ich sehr dankbar.

lg mexxwell33

[solkar]

Hallo!

Die Subquery

(SELECT SUBDATE(MAX(time), INTERVAL 15 MINUTE) AS c_time,
                          messpunkt AS m2 FROM messungen) AS r_t

sieht so aus als wolltest Du max(time)-15m PRO MESSPUNKT ermitteln; das tut sie aber nicht, sondern sie ermittelt max(time)-15m auf der ganzen Tabelle!

Sorry habe vergessen das group by mit ein zu tippen!

So ist das nicht zielführend; "messpunkt" steht in der Query schon wieder zweimal in SELECTs ohne "AS" und

Die Abfrage sieht wie folgt aus:

das scheint mir nicht plausibel, denn

SELECT id, time, messpunkt, data FROM messungen as l_t
      INNER JOIN (select SUBDATE(max(time), INTERVAL 15 MINUTE) as c_time,
                          messpunkt FROM messungen
                  group by messpunkt ) as r_t
      ON l_t.messpunkt = r_t.messpunkt
WHERE  l_t.time > r_t.c_time
ORDER BY time DESC

MySQL beschwert sich zu Recht darüber:

"ERROR 1052 (23000) at line 1: Column 'messpunkt' in field list is ambiguous"

Bitte die Query genauso angeben, wie Du sie bislang verwendest!
Cut & Paste!

Da es ca. 700 Messpunkte

Aha!

und daher eine Menge an Daten in der Tabelle vorhanden

Dass da "eine Menge" ist war mir irgendwie schon klar.... ;-)
Und genau die Mächtigkeit dieser Menge solltest Du uns mitteilen, genau wie die restlichen Infos, die ich bereits genannt hatte:

Wichtig wäre es mal die Datentypen und Indices zu betrachten:

• Typ von messpunkt? Indiziert?
• Typ von data? Ist das ein Blob? Falls ja, wie groß im Mittel?

Insgesamt wäre natürlich das Mengengerüst wichtig:

• Aktuelle Zahl der Datensätze?

Zusätzlich wäre die Angabe der DB-Engine (MyISAM? InnoDB? ...?) zielfühend.

Grüsse

Solkar

[mexxwell33]

Sorry hier nochmal das Statement mit C&P:

SELECT l_t.id, l_t.time, l_t.messpunkt, l_t.data FROM messungen as l_t
       INNER JOIN (select SUBDATE(max(time), INTERVAL 15 MINUTE) as c_time,
                           messpunkt FROM messungen
                   group by messpunkt ) as r_t
     ON l_t.messpunkt = r_t.messpunkt
 WHERE  l_t.time > r_t.c_time
ORDER BY time DESC

Benötigt ewig lange, daher habe ich die Abfrage abgebrochen.

Mit zusätzlicher Einschränkung des Zeitraum z.B. nur Datensätze ab xxx durchsuchen dauert die Abfrage nur einige Sekunden.

SELECT l_t.id, l_t.time, l_t.messpunkt, l_t.data FROM messungen as l_t
       INNER JOIN (select SUBDATE(max(time), INTERVAL 15 MINUTE) as c_time,
                           messpunkt FROM messungen
                   where time > '2008-08-13'
                   group by messpunkt ) as r_t
     ON l_t.messpunkt = r_t.messpunkt
 WHERE  time > '2008-08-13' AND
        l_t.time > r_t.c_time
ORDER BY time DESC

Wäre im Prinzip OK, dennoch bin ich damit nicht ganz happy, denn ich hätte gerne eine Abfrage, die mir die letzten x Messungen aller Messpunkte ermittelt.
Mit meiner Abfrage bekomme ich zwar brauchbare Ergebnisse, jedoch kann es sein das einige Messpunkte seit längerer Zeit daher < als r_t.c_time keine Messungen mehr geliefert haben und daher nicht im Ergebnis erhalten sind.

Diese müsste ich dann einzeln mit beispielsweise folgendem Query ermitteln.

SELECT id, time, messpunkt, data FROM messungen
 WHERE messpunkt = 'AS71_lmz_093'
 ORDER BY time DESC
 Limit 10

Nun habe ich es auf einen anderen Weg probiert und zwar, bastle ich über die Applikation ein Abfrage mit einer Menge an UNIONs (700 ;-)). Wie nachfolgend gezeigt. Dies bringt zwar das gewünschte Ergebnis, dauert aber noch immer ziemlich lange ca. 3 Minuten :-( . Mit Zeiteinschränkung im WHERE z.B auf nur DS der letzten paar Tage zu durchsuchen, bringe ich die Abfrage auf ~10s. Nur müsste ich dann wieder fehlende Messungen einzeln abfragen.

(SELECT id, time, messpunkt, data FROM messungen
 WHERE messpunkt = 'AS71_lmz_091'
ORDER BY time DESC Limit 10)
UNION
(SELECT id, time, messpunkt, data FROM messungen
 WHERE messpunkt = 'AS71_lmz_092'
ORDER BY time DESC Limit 10)
UNION
...

Gibt es nicht eine elegantere Lösung?

Hier noch die Infos zur DB:
 Wie gesagt mysql 4.1
 Datensätze in der Tabelle: momentan 3661100
 Engine ist InnoDB
 Indiziert ? (Index ist drauf, kenne mich damit aber leider (noch) nicht aus)
 Data ist lediglich vom Typ int

Möchte mich schon mal im vorhinein für deine / eure Hilfe bedanken!

lg mexxwell33

[solkar]

Hallo!

Hier noch die Infos zur DB:
Wie gesagt mysql 4.1
Datensätze in der Tabelle: momentan 3661100
Engine ist InnoDB
Indiziert ? (Index ist drauf, kenne mich damit aber leider (noch) nicht aus)
Data ist lediglich vom Typ int

Z.B. mysqldump --no-data liefert Info zu Struktur einer DB.

WICHTIG : DIE "--no-data" OPTION BEACHTEN!

Wenn die fehlt, wird ein kompletter Table-Dump erzeugt und der könnte etwas  groß sein... ;)

Ich hab mir (ABER UNTER "6.05-alpha-community" !) mal eine DB und eine ähnliche Tabelle erzeugt, Indices auf id(PK) und time und messpunkt gesetzt

  
DROP TABLE IF EXISTS messungen;  
  
CREATE TABLE messungen (  
  
    id INT auto_increment PRIMARY KEY,  
  
--  Btw: Reservierte Wörter bitte nicht als Namen verwenden! "dt" wäre nicht uebel.  
    time DATETIME,  
--  
    messpunkt CHAR(12),  
  
--  Btw: Reservierte Wörter bitte nicht als Namen verwenden! "dat" wäre nicht uebel.  
    data INT  
--  
  
) Engine=InnoDB;  
  
CREATE INDEX idx_messungen_messpunkt ON messungen(messpunkt);  
CREATE INDEX idx_messungen_time ON messungen(time);  

und mit 3.4 Mio Datensätze befüllt; dann einmal gemessen:

  
  
--  Die Messungen wurden jeweils nach restart vom mysql-Server durchgeführt  
--  um vergleichbare Cache-Zustaende zu gewaehrleisten  
  
--  Um Grundlast zu erzeugen und lies ich parallel einen Rendering-Job im  
--  Endlos-Batch laufen  
  
--  1 Laufzeiteinheit LZE := Laufzeit von  
--  SELECT COUNT(id) FROM messages;  
  
    SELECT m1.id, m1.time, m1.messpunkt, m1.data  
    FROM messungen m1  
    INNER JOIN (SELECT SUBDATE(MAX(time), INTERVAL 15 MINUTE) AS c_time, _m.messpunkt  
                    FROM messungen _m  
                    GROUP BY _m.messpunkt  
                ) m2  
        ON (m1.messpunkt = m2.messpunkt)  
    WHERE m1.time > m2.c_time;  
    ORDER BY m1.time DESC;  
  
/*  
    MIT INDICES auf messpunkt und time laeuft die Query ca 17 LZE  
    Die Subquery traegt mit ca 8 LZE bei.  
  
    Weglassen der ORDER-clause beschleunigt die Query kaum (0.1 LZE)  
        Das ist auch einsichtig, da auf "time" ein Index liegt den MySQL  
        schon beim JOIN zum Scan von messages (m1) benutzen kann.  
*/  
  
/*  
    MIT INDICES AUF messpunkt ABER OHNE INDEX auf time lief die Query ca 22 LZE  
    Die Subquery trug mit ca 10 LZE bei.  
  
    Weglassen der ORDER-clause VERLANGSAMTE die Query sogar gerungfügig (0.2 LZW)  
    Sieht nach einem Messfehler aus.  
*/  
  
/*  
    OHNE INDEX AUF messpunkt UND OHNE INDEX hatte ich keine Wahl als bei 98%  
    Prozessorlast den MySQL-Prozess zu killen.  
  
    Das kann ein Bug von 6.0.5-alpha sein, aber ich glaub's nicht.  
  
*/  

Das war zwar nur eine Messreihe, aber wir sollten jetzt erstmal klären (s.o.) wie die DDL genau ausschaut; insbesondere ob und wie Indices angelegt sind!

Mit zusätzlicher Einschränkung des Zeitraum z.B. nur Datensätze ab xxx durchsuchen dauert die Abfrage nur einige Sekunden.

SELECT l_t.id, l_t.time, l_t.messpunkt, l_t.data FROM messungen as l_t
       INNER JOIN (select SUBDATE(max(time), INTERVAL 15 MINUTE) as c_time,
                           messpunkt FROM messungen
                   where time > '2008-08-13'
                   group by messpunkt ) as r_t
     ON l_t.messpunkt = r_t.messpunkt
WHERE  time > '2008-08-13' AND
        l_t.time > r_t.c_time
ORDER BY time DESC

Wäre im Prinzip OK, dennoch bin ich damit nicht ganz happy, denn ich hätte gerne eine Abfrage, die mir die letzten x Messungen aller Messpunkte ermittelt.

Das ist verständlich.

Mit meiner Abfrage bekomme ich zwar brauchbare Ergebnisse, jedoch kann es sein das einige Messpunkte seit längerer Zeit daher < als r_t.c_time keine Messungen mehr geliefert haben und daher nicht im Ergebnis erhalten sind.

Deshalb machen wir das.

Diese müsste ich dann einzeln mit beispielsweise folgendem Query ermitteln.

SELECT id, time, messpunkt, data FROM messungen
WHERE messpunkt = 'AS71_lmz_093'
ORDER BY time DESC
Limit 10

Nun habe ich es auf einen anderen Weg probiert und zwar, bastle ich über die Applikation ein Abfrage mit einer Menge an UNIONs (700 ;-)). Wie nachfolgend gezeigt. Dies bringt zwar das gewünschte Ergebnis, dauert aber noch immer ziemlich lange ca. 3 Minuten :-( . Mit Zeiteinschränkung im WHERE z.B auf nur DS der letzten paar Tage zu durchsuchen, bringe ich die Abfrage auf ~10s. Nur müsste ich dann wieder fehlende Messungen einzeln abfragen.

(SELECT id, time, messpunkt, data FROM messungen
WHERE messpunkt = 'AS71_lmz_091'
ORDER BY time DESC Limit 10)
UNION
(SELECT id, time, messpunkt, data FROM messungen
WHERE messpunkt = 'AS71_lmz_092'
ORDER BY time DESC Limit 10)
UNION
...

;-)

Das sieht nicht wirklich attraktiv aus...

Kannst Du die Datenstruktur eigentlich noch ändern oder ist das System jetzt mit Daten online ?

Hast Du ein befülltes Backup-System?

Ein Ansatz könnte sein

• die Daten zu normalisieren
• indem messungen eine eigene Tabelle erhält
• mit einem Feld für den lezten zeitstempel
• und dieser programmatisch gesetzt wird, 4.1 unterstützte ja noch keine Trigger (leider)

Da die Subquery mit 40%-50% zu den LZ Kosten beiträgt könnte man, grob geschätzt die Laufzeit entsprechend reduzieren.

Btw - warum hast Du solch eine niedrige MySQL-Version im Einsatz?

Grüsse

Solkar

[Solkar]

Streiche:

und mit 3.4 Mio Datensätze befüllt; dann einmal gemessen:

Setze:

und mit 3.4 Mio Datensätzen befüllt; dann einmal gemessen:

Wie ich solche Typos hasse... :-(

[mexxwell33]

Mit der Subquery erhalte ich den Zeitpunkt für die Messungen vor 15 Minuten, und den verwende ich in der äußeren Abfrage als Prüfung. (l_t.time > r_t.c_time)
Somit bekomme ich die letzten 10 Messungen aller Stationen.
Da es ca. 700 Messpunkte und daher eine Menge an Daten in der Tabelle vorhanden sind dauert die Abfrage aber ewig lange :-(

(Limit ist in MySql 4.1 Subqueries nicht unterstützt.....)

Ich versuche das Problem nun mal mit mehreren Abfragen zu lösen, wenn mir aber jemand einen Tipp geben könnte wäre ich sehr dankbar.