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

[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