[MySQL] Optimierung select-Statement mit where

[domeemod]

Hallo zusammen,

ich habe eine Frage zu dem folgend Statement:

SELECT
SELECT
  rc_orders.Timestamp_PLC_Start AS Datum_start,
   rc_orders.ID_Function,
   sum(rc_values.Stoptime),
   count(*)
  
FROM    rc_orders, rc_reports, rc_values, rc_functions
  
WHERE    rc_orders.Timestamp_PLC_End between rc_reports.Starttime and rc_reports.Endtime
     and rc_orders.ID_Machine = 3
     and (rc_values.Timestamp_PLC between rc_orders.Timestamp_PLC_start and rc_orders.Timestamp_PLC_End)

group by  DATE(rc_orders.Timestamp_PLC_End)

Die Abfrage auf der lokalen DB braucht ewig (127 Sekunden) und ich würde gerne wissen, ob man diese optimieren kann.
Die Tabelle rc_values hat ~230.000 Werte.


Gruß
domeemod

 

[Yaslaw]

item: Die Zeile 1 ist wohl zuviel. Ein SELECT sollte reichen

item: Dein GROUP BY wird zwar so von MySQL akzeptiert, ist aber alles andere als Richtig. MySQL ist da einfach zu gnädig (in jeder anderen DBMS würd da ein Fehler kommen). Du gruppierst nach einem Feld, das du nachher nicht mal ausgibst. Ist zwar kein Fehler, macht aber wenig Sinn. Der Fehler ist: Du hast 2 Ausgabefelder bei denen nicht definiert ist, wie sich sich beim GROUP BY verhalten sollen

item: Du hast eine Quelle rc_functions, die weder verknüpft noch anderwertig verwendet wird. Weg damit

item: Du machst ein COUNT über eh alles. Es reicht ihn über die rc_orders Tabelle zu legen

item: Hast du Index auf den Tabellen?

 

[domeemod]

Danke für die schnelle Antwort.

Wurden paar Fehler beim einfügen des Statements hier gemacht.

Statement ist nun:

SELECT
  rc_orders.Timestamp_PLC_End AS Datum_start,
   rc_orders.ID_Function,
   sum(rc_values.Stoptime),
   count(rc_orders.ID)

FROM    rc_orders, rc_reports, rc_values

WHERE    rc_orders.Timestamp_PLC_End between rc_reports.Starttime and rc_reports.Endtime
     and rc_orders.ID_Machine = 3
     and (rc_values.Timestamp_PLC between rc_orders.Timestamp_PLC_start and rc_orders.Timestamp_PLC_End)

group by  DATE(rc_orders.Timestamp_PLC_End), rc_orders.ID_Function

Habe Index in den Tabellen.
Bin nun runter auf 18 Sekunden!

 

[domeemod]

Hallo ich habe hier noch eine Abfrage die EXTREM lange dauert.

Das Statement liefert bei wenigen Einträgen in der Tabelle rc_values die gewünschten Ergebnisse zeitnah.
Bei ca. 200.000 Datensätzen dauert die Abfrage jedoch > 5Minuten.
Die Prozessorlast liegt < 40%.



Hier das mySQL-Statement:

SELECT
   
    rc_orders.Timestamp_PLC_End,
    rc_values.ID_Function,
    rc_values.Timestamp_PLC,
    SUM(rc_values.Stoptime),
    COUNT(rc_values.ID_Function),
    DATE_SUB(rc_orders.Timestamp_PLC_End, interval rc_orders.Laufzeit_delta minute) AS PLC_Start

FROM     rc_orders, rc_reports, rc_values
  
WHERE    
       
        rc_values.Timestamp_PLC between rc_reports.Starttime and rc_reports.Endtime
        and rc_values.ID_Machine = 3
        and rc_values.Timestamp_PLC between  DATE_SUB(rc_orders.Timestamp_PLC_End, interval rc_orders.Laufzeit_delta minute)  and  rc_orders.Timestamp_PLC_End
       


group by     DATE(rc_values.Timestamp_PLC), rc_values.ID_Function

 

[domeemod]

Vielleicht zur Info:
Das Statement für auf welche Störung (rc_values.ID_Funktion) im Zeitrahmen (rc_reports.Starttime und rc_reports.Endtime) in Abhänigkeit des Zeitstempels der Maschine (rc_values.Timestamp_PLC), aber nur von der Maschine Nr.3.

Das muss doch bestimmt eleganter gehen wie oben oder?

 

[Biber3]

Moin domeemod,

falls du dich fragst, warum yaslaw nicht mehr so schnell antwortet, wie es sonst seine Art ist, hätte ich zwei, drei Erklärungsansätze:

a) er hat das Gefühl, dass du seine Antworten gar nicht liest ( zum Beispiel sein Item über unsinnige und eigentlich ungültige GROUP BYs ) oder seine Rückfragen nicht verstehst (zum Beispiel seine Frage "Hast du Index auf den Tabellen?", die du umfassend und informativ mit "Habe Index in den Tabellen" beantwortet hast).

b) und/oder er hat gestern - genau wie ich- ins Essen gebrochen, als sich in deinem zweiten Statement herausstellte, dass das Feld "ID_Machine" sowohl in rc_orders wie auch in rc_values vorhanden ist, ein Join allerdings über eine aberwitzige Näherungslösung mit 3x konvertierten Start/End-Zeiten simuliert wird.
Da kommen sich sensible Mitmenschen ein wenig veralbert vor.

c) Vielleicht ist yaslaw aber auch nur zögerlich, einen Join über drei Tabellen "zu optimieren", wenn die Abhängigkeiten zwischen diesen drei Tabellen nicht bekannt sind.

Geh bitte 30cm nach oben, lies nochmal yaslaws Kommentare zum ersten Statement und versuche bitte gedanklich zuzulassen, dass sie eventuell auch auch auf das zweite Copy&Paste-Clonestatement gelten könnten.

Grüße
Biber

@Yaslaw: Bitte, lösch meinen Kommentar, wenn du es ihm schonender beibringen kannst.
Aber loswerden wollte ich es einmal.

 

[domeemod]

Tut mir Leid, wenn ich etwas ungestüm war. Gelobe Besserung.
Ok ich versuche e nun Schritt für Schritt.

@Indexen:
alle Tabellen haben Index in Form eines Primary Keys der eindeutig ist.
Somit sollten die Tabellen alle indexiert sein.

@select:
Das zweite SELECT ist hier fälschlicherweise drin. Wohl beim ZU schnellen copy & paste und bearbeiten dazu gerutscht.

@Group by:
So wie ich das jetzt verstehe passen DATE(rc_values.Timestamp_PLC) und rc_values.ID_Function" in die Abfrage, aber group by weiß nicht was es mit der summe und count tun soll . richtig?

@rc_function
ist entfernt, da nicht benötigt wird.

@CoUnT:
habe ich geändert in dem ich eine einzelne Spalte zähle:
COUNT(rc_values.ID_Function)


Generell ist das Problem, dass ich bis auf die Zeiten keine Verknüpfung zu der Tabelle rc_values zu den anderen Tabellen habe (außer der ID_Maschine)


zur besseren Darstellung habe ich noch das ERM angehängt.

Hoffe die Antwort ist nun etwas besser.


Gruß
domeemod.

 

[domeemod]

Die Grafik allein ist denke ich immer noch etwas schwer zu interpretieren. Deshalb hab ich noch eine ERM erstellt mit Kommentaren zu den Tabellen:

Und das Ergebnis sieht dann so aus:
'2014-07-07 11:57:00', '2', '2014-07-07 05:02:00', '26.7833', '4', '2014-07-07 05:01:00'
'2014-07-07 11:57:00', '3', '2014-07-07 05:08:00', '174.08331700000005', '81', '2014-07-07 05:01:00'
'2014-07-07 15:15:00', '4', '2014-07-07 15:10:00', '14.066662999999998', '4', '2014-07-07 13:47:00'
'2014-07-07 05:00:00', '99', '2014-07-07 05:00:00', '29.24995000000001', '6', '2014-07-07 05:00:00'

Als Bild lesbarer:

Erster Datensatz:
Es trat im Zeitraum (von rc_report Start und Endtime) 4mal eine Störung vom Typ 2 auf mit einer Dauer von 26,7 Minuten.

 

[HonniCilest]

Ich weiß die letzte Antwort ist bereits fast eine Woche alt, wenn ich mich ggf. dennoch einmischen dürfte...

1. Je nach Anwendungsfall ist es zu überlegen, ob man bei Queries mit Funktionen wie SUM einen View erstellt.

Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.

Oder anders gesagt, die Ergebnisse würden bereits Datenbankseitig vorgehalten und allen Benutzern bereits berechnet zur Verfügung stehen. Als Ergebnis ist die Abfrage vom Frontend schneller.

2. Ich habe deine Benennung mal verwendet um etwas zu verdeutlichen.
Ausgangspunkt:

SELECT rc_values.Timestamp_PLC, rc_reports.Starttime, rc_reports.Endtime
FROM rc_reports, rc_values
WHERE rc_values.Timestamp_PLC BETWEEN rc_reports.Starttime AND rc_reports.Endtime;

FROM X, Y ist meines Erachtens nichts Anderes als ein CROSS JOIN, d.h. nach meinem Verständnis werden datenbankseitig alle Einträge von X mit allen Einträgen von Y kombiniert und anschließend gefiltert.
Das Gleiche Ergebnis wie das Statement oben bringt jedoch auch:

SELECT rc_values.Timestamp_PLC, rc_reports.Starttime, rc_reports.Endtime
FROM rc_reports
JOIN rc_values
ON rc_values.Timestamp_PLC BETWEEN rc_reports.Starttime AND rc_reports.Endtime;

Hier wird nach meinem Verständnis direkt beim JOINEN auf die Bedingung geprüft, ich bin mir also ziemlich sicher, dass die 2. Variante schneller ist als die 1., wenn vielleicht auch nicht bemerkenswert.

Bitte korrigiert mich falls ich hier falsch liege ;-)

 

[domeemod]

Danke für diese Informationen. Werde ich mal testen und schauen ob es einen Geschwindigkeits"boost" gibt.
Wobei die Abfrage momentan auf einem i5 Prozessor mittlerweile nur paar Sekunden dauert.
Auf meinem alten CoreDuo jedoch erheblich länger. Glaub waren 6 Minuten.

Auf dem Zielrecner (i5) sind die Zugriffszeiten somit ok.
Nur braucht das Reporttool "Birt" extrem lange zum erzeugen des Reports.
Das sollte ich aber bei denen im Forum posten.
Hier geht es nur um das SQL-Statement.