Abfrage von Werten aus 2 Tabellen dauert lange

[Micha]

Hi,

ich habe zwei Tabellen in einer MYSQL-DB. Die eine enthält Korrekturwerte, die andere enthält Messungen. Die Daten wurden beide mit einem Zeitstempel versehen und dann epochenweise abgelegt. In der Tabelle mit den Korrekturwerten existiert je ein Wert pro Epoche. Messungen sind jedoch mehrere pro Epoche vorhanden. Ferner konnte nicht zu jeder Epoche gemessen werden. Ich versuche nun zu jeder Messepoche einen entsprechenden Korrekturwert aus der anderen Tabelle zu bekommen. Das funktioniert zwar bereits aber dauert extrem lange. Im Moment sieht meine Abfrage wie folgt aus:

$query = "SELECT korrekturFROMkorrekturwerte RIGHT JOIN (messungen) ON (messungen.epoche=korrekturwerte.epoche) GROUP BY messungen.epocheORDER BYkorrekturwerte.epoche ASC";

Bei dem Wert epoche handelt es sich um einen einfachen Integer. Die Tabelle der Korrekturwerte enthält fast 10x soviel Epochen im Vergleich zu den Messwerten. Ferner wird der (eher unwahrscheinlichere aber denkbare) Fall: kein Korrekturwert zur Messung, bisher nicht behandelt.

Da die aktuelle Abfrage jedoch extrem lange dauert, würde ich mich hier über Verbesserungsvorschläge freuen. Vielleicht ist der aktuelle Versuch eher etwas überladen - mein SQL-Wissen ist zugegebenermaßen eher rar. ;-)

Mit freundlichem Gruß
Micha

[Sven Rautenberg]

Moin!

$query = "SELECT korrekturFROMkorrekturwerte RIGHT JOIN (messungen) ON (messungen.epoche=korrekturwerte.epoche) GROUP BY messungen.epocheORDER BYkorrekturwerte.epoche ASC";

Wann immer ein Query sehr lange dauert, ist es der erste Schritt in der Analyse, dass man einen weiteren Query absenden, bei dem man "EXPLAIN" vor den langsamen Query schreibt. Das wirft dir die Ausführungsstrategie aus, die die Datenbank benutzt - besonders wichtig sind die Informationen über genutzte Indices.

Wenn du mit der Ausgabe und den Infos im Handbuch nichts anfangen kannst, gehört die Explain-Ausgabe hier ins Forum.

Aber ich vermute einfach mal was: Deine beiden Spalten für Epoche sind nicht als Index definiert.

- Sven Rautenberg

[Micha]

Hallo Sven Rautenberg,

Wann immer ein Query sehr lange dauert, ist es der erste Schritt in der Analyse, dass man einen weiteren Query absenden, bei dem man "EXPLAIN" vor den langsamen Query schreibt. Das wirft dir die Ausführungsstrategie aus, die die Datenbank benutzt - besonders wichtig sind die Informationen über genutzte Indices.

Wenn ich EXPLAIN SELECT korrekturFROMkorrekturwerte RIGHT JOIN (messungen) ON (messungen.epoche=korrekturwerte.epoche) GROUP BY messungen.epocheORDER BYkorrekturwerte.epoche ASC ausführe in phpmyadmin erhalte ich folgende Tabelle:

  
id      select_type       table           type   possible_keys       key       key_len       ref       rows       Extra  
1          SIMPLE      messungen           ALL      NULL            NULL         NULL        NULL      27280      Using temporary; Using filesort  
1          SIMPLE      korrekturwerte      ALL      NULL            NULL         NULL        NULL      3374  

gehört die Explain-Ausgabe hier ins Forum.

Ist es das, was Du gesucht hast (und hilft es Dir weiter)?

Aber ich vermute einfach mal was: Deine beiden Spalten für Epoche sind nicht als Index definiert.

Ja, hier liegst Du richtig. Dies sollte ist vermutlich tun?

Mit freundlichem Gruß
Micha

[Rouven]

Hello,

Ja, hier liegst Du richtig. Dies sollte ist vermutlich tun?

Genau, stell dir dich beim Suchen eines Buches in einer Bibliothek mit 27280 Büchern vor - neben dem Zuordnen der Autorenprofile sollst du die Dinger auch gerade noch nach Erscheinungsjahr sortieren. Haken: du kriegst keinen Katalog.
Genau das sagt dir das EXPLAIN gerade:

• possible_keys: null
• Extra: Using temporary (AKA ich brauch eine temporäre Tabelle zur Sortierung)
• Extra: Using filesort (AKA ich musste der Reihe nach durchgehen)
  Man könnte z.B. mal einen (UNIQUE/NON-UNIQUE musst du auf Grund der Datenstruktur entscheiden) Index auf messungen.epoche anlegen.

MfG
Rouven

[Micha]

Hallo Rouven,

vielen Dank für Deine anschauliche Erklärung. Ich werde es heute Abend testen und entsprechendes Feedback geben.

Man könnte z.B. mal einen (UNIQUE/NON-UNIQUE musst du auf Grund der Datenstruktur entscheiden) Index auf messungen.epoche anlegen.

Bei den Korrekturwerten wäre UNIQUE vll noch denkbar. In der Tabelle der Messungen nicht, da zu einer Epoche mehrere Messungen existieren (die auch zusammengehören), die den selben Korrekturwert benötigen. Die Tabelle der Messungen sieht vereinfacht so aus:

id   epoche   wert1  ....  
 1    1      47.11  
 2    1       8.15  
 3    1       3.14  
 4    5      46.12  
 5    5       8.14  
 6    5       3.15  
...  

Zur Erklärung: Die Korrektur ist zur Kompensation einer temperaturbedingten Ausdehnung eines Objektes. Die Messungen am Objekt laufen zwar nach einander ab aber immerhin noch so schnell, dass sie als "zeitgleich" angesehen werden können bzw. die Ausdehnung während der Messung nicht signifikant ist.

Mit freundlichem Gruß
Micha

[MudGuard]

Hi,

$query = "SELECT korrekturFROMkorrekturwerte RIGHT JOIN (messungen) ON (messungen.epoche=korrekturwerte.epoche) GROUP BY messungen.epocheORDER BYkorrekturwerte.epoche ASC";

Welchen Sinn soll es haben, nach messungen.epoche zu gruppieren, wenn keine Aggregat-Funktionen selektiert sind (und auch nicht die gruppierte Spalte)?
Daß das überhaupt zu einem Ergebnis führt, liegt an MySQL - andere DBMS hätten dir eine Fehlermeldung geliefert.

cu,
Andreas

[Micha]

Hallo MudGuard,

Welchen Sinn soll es haben, nach messungen.epoche zu gruppieren,

Ich wollte damit erreichen nur Messepochen zu bekommen, die unterschiedlich sind.

Mit freundlichem Gruß
Micha

[Micha]

Hallo Sven und Rouven,

ich habe Euren Rat befolgt und nun flutscht es wieder ;-) Besten Dank!

Mit freundlichem Gruß
Micha