JOIN-Query sehr langsam

[Sven]

Hallo,

ich habe eine SQL-Anweisung mit einem LEFT JOIN, bei der die Ausführung über 20 Sekunden dauert!

Dabei ist die Anweisung nicht mal sehr komplex:

SELECT *, berater.id as id, staedte.id as stadt_id FROM berater LEFT JOIN staedte ON plz_von <= berater.plz and plz_bis >=berater.plz

In der Tabelle "berater" sind 370 Datensätze und in der Tabelle "staedte" ca 13000.

Wie kann man die Abfrage optimieren? Die im LEFT JOIN verwendeten Spalten plz_von und plz_bis und plz habe ich schon indiziert.

Ich nutze diese Abfrage für eine Umkreissuche.

Viele Grüße,
Sven

[flashnfantasy]

Wenn ich die Query richtig verstehe, dann willst du alle Berater ausgeben, und zusätzlich dazu sollen die Städte ausgegeben werden, in denen die Berater tätig sind ?
Vermutlich wird das Left Join im Zusammenhang mit dem größer/kleiner einen Full-Table-Scan machen.

Vielleicht hilft dir folgende Funktion weiter.
Du übergibst den Query-String und sie zeigt an, wie groß die Kardinalität der Abfrage ist.
Mit dem Optionalen 2.ten Parameter =1 kriegst du dann auch den Ergebnisswert angezeigt.
Dann siehst du auch, ob die Indizierung genutzt wird.

function analyseQuery ($query, $mode = 0)
{
  $ergq = mysql_query("EXPLAIN $query");
  print "\n\n<!-- Explain -->\n<table border="1" cellspacing="0">";
    print "<tr>";
    print "<td colspan="8" class="tinytableCC">".hiliSQL(htmlentities($query))."</td>";
    print "</tr>";
    print "<tr>";
    print "<td class="tinytableCC">table</td>";
    print "<td class="tinytableCC">type</td>";
    print "<td class="tinytableCC">possible_keys</td>";
    print "<td class="tinytableCC">key</td>";
    print "<td class="tinytableCC">key_len</td>";
    print "<td class="tinytableCC">ref</td>";
    print "<td class="tinytableCC">rows</td>";
    print "<td class="tinytableCC">Extra</td>";
    print "</tr>";
  $rowsprod = 1;
  while ($dsatz = mysql_fetch_array($ergq))
  {
    if (isset($dsatz["Comment"]))
    {
      print "\n\t<tr><td colSpan="8" class="tinytableCC">".$dsatz["Comment"]."</td></tr>";
    }
    else
    {
      $rows = $dsatz["rows"];
      $rowsprod *= $rows;
      print "\n\t<tr>";
      print "<td class="tinytableCC">".$dsatz["table"]."</td>";
      print "<td class="tinytableCC">".$dsatz["type"]."</td>";
      print "<td class="tinytableCC">".$dsatz["possible_keys"]."</td>";
      print "<td class="tinytableCC">".$dsatz["key"]."</td>";
      print "<td class="tinytableCC">".$dsatz["key_len"]."</td>";
      print "<td class="tinytableCC">".$dsatz["ref"]."</td>";
      print "<td class="tinytableCC">$rows</td>";
      print "<td class="tinytableCC">".$dsatz["Extra"]."</td>";
      print "</tr>";
    }
  }
    print "<tr>";
    print "<td colspan="8" class="tinytableCC">Anzahl Abfragen = $rowsprod</td>";
    print "</tr>";
  if ($mode == 1)
  {
    $ergqr = mysql_query("$query");
    $c = queryrows($ergqr);
    print "<tr>";
    print "<td colspan="8" class="tinytable">Anzahl Treffer = $c</td>";
    print "</tr>";
  }
  print "</table>";
}

[Sven]

Vermutlich wird das Left Join im Zusammenhang mit dem größer/kleiner einen Full-Table-Scan machen.

Mit der SQL-Anweisung BETWEEN geht es gleich lang:

SELECT *, berater.id as id, staedte.id as stadt_id FROM berater LEFT JOIN staedte ON berater.plz between plz_von and plz_bis GROUP BY berater.ort ORDER BY berater.id DESC

Habe deinen Code ausgeführt, in Tabelle "berater" steht als Extra "Using temporary; Using filesort". Vielleicht nützt dir die Info was?

In "possible_keys", "key", "key_len" und "ref" steht nichts. Bedeutet das nun, dass keine Indizierung verwendet wird?

Dann steht noch Anzahl Abfragen = 4906940 da.
Wieso macht der LEFT JOIN 5 Mio Abfragen?

[wahsaga]

hi,

Dann steht noch Anzahl Abfragen = 4906940 da.
Wieso macht der LEFT JOIN 5 Mio Abfragen?

Deswegen:

In der Tabelle "berater" sind 370 Datensätze und in der Tabelle "staedte" ca 13000.

Es werden zunächst alle möglichen Kombinationen gebildet, und das sind bei den genannten Zahlen 4810000 Stück, bzw. im Umkehrschluss, wenn die 370 feststeht und 4906940 die tatsächliche Anzahl ist, dann müsste deine Städte-Tabelle genau 13262 Einträge enthalten.

gruß,
wahsaga

[Sven]

Es werden zunächst alle möglichen Kombinationen gebildet, und das sind bei den genannten Zahlen 4810000 Stück, bzw. im Umkehrschluss, wenn die 370 feststeht und 4906940 die tatsächliche Anzahl ist, dann müsste deine Städte-Tabelle genau 13262 Einträge enthalten.

Bingo.

Vermutlich dauern diese 5 Mio Anfragen so lange...
Wenn ich berater.plz = staedte.plz setzen könnte, ginge die Abfrage sehr schnell - also ist das Problem die BETWEEN-Anweisung.

Kann man die SQL-Anweisung irgendwie anders formulieren? Muss ich einen anderen JOIN-Typ verwenden? Muss ich WHEN - THEN oder IF-Anweisungen in meinem SQL benutzen??

[wahsaga]

hi,

Wenn ich berater.plz = staedte.plz setzen könnte, ginge die Abfrage sehr schnell - also ist das Problem die BETWEEN-Anweisung.

Hast du denn einen Index auf staedte.plz liegen?

Wenn nein, solltest du einen erstellen.
Wenn doch, dann wäre zu analysieren, warum er für diese Abfrage - laut EXPLAIN - nicht genutzt werden kann.

gruß,
wahsaga

[Sven]

Ich hab nen Index auf staedte.plz_von und staedte.plz_bis - wer analysiert mir nun, wieso der Index nicht genutzt wird?? ;)

[globe]

n'abend,

Ich hab nen Index auf staedte.plz_von und staedte.plz_bis - wer analysiert mir nun, wieso der Index nicht genutzt wird?? ;)

ist das _ein_ Index (Concatenated Index), der beide Spalten beinhaltet, oder sind das zwei eigenständige Indexe? letzteres bringt dir erstmal gar nix. MySQL kann nur einen Index pro Tabelle pro Abfrage nutzen.

weiterhin schönen abend...

[Sven]

Hi,

es waren zwei eigenständige Indizes.

Habe nun einen weiteren Index, der über beide Spalten geht, eingefügt. Die SQL-Anweisung dauert noch immer gleich lange.
Außerdem meckert phpmyadmin nun "Es sollte nicht mehr als ein Index des Typs INDEX für die Spalte plz\_von gesetzt sein".

Wenn ich ein EXPLAIN auf die SQL-Anweisung mache, wird nicht ersichtlich, dass ein Index verwendet wird.

In phpmyadmin sehe ich außerdem, dass die Kardinalität meines Indexes über plz_von und plz_bis 6631 ist - in der Tabelle sind aber doppelt so viele Datensätze! Ist das normal?

[Sven]

Ich habe die Anfrage nun soweit optimiert, dass sie halb so lange dauert.
Dazu frage ich erst alle Berater aus der Datenbank ab und in einer foreach-Schleife dann die Städte dazu.

Nun habe ich statt einer SQL-Anweisung (mit dem LEFT JOIN) ca. 370 SQL-Aufrufe.

Das Problem ist eindeutig die BETWEEN-Anweisung:
SELECT * FROM staedte WHERE $berater[plz] between plz_von and plz_bis

Deshalb habe ich die PHP-Datei nochmals überarbeitet:
$result_staedte = $db->query("SELECT * FROM staedte WHERE plz_von = $berater[plz]");
if (!mysql_num_rows($result_staedte))  {
 $result_staedte = $db->query("SELECT * FROM staedte WHERE plz_bis = $berater[plz]");
}
if (!mysql_num_rows($result_staedte)) {
 $result_staedte = $db->query("SELECT * FROM staedte WHERE $berater[plz] between plz_von and plz_bis");
}

Nun wird die BETWEEN-Anweisung erst ausgeführt, wenn die anderen zwei Anweisungen kein Ergebnis bringen. Nun habe ich 70 SQL-Abfragen mehr aber ich bekomme Ruck-Zuck ein Ergebnis.

Mich würde es aber sehr interessieren, wieso die BETWEEN-Anweisung solange dauert oder ob es eine Lösung mit nur einer SQL-Anweisung für dieses Problem gibt.

[Vinzenz Mai]

Hallo Sven,

Dazu frage ich erst alle Berater aus der Datenbank ab und in einer foreach-Schleife dann die Städte dazu.

Nun habe ich statt einer SQL-Anweisung (mit dem LEFT JOIN) ca. 370 SQL-Aufrufe.

Gibt es Berater, denen keine Städte zugeordnet sind?
Wenn ja, bist Du an diesen interessiert?
Wenn nein, warum verwendest Du einen LEFT JOIN?

Das Problem ist eindeutig die BETWEEN-Anweisung:
SELECT * FROM staedte WHERE $berater[plz] between plz_von and plz_bis

Das ist völlig logisch und normal. Du hast keine Join-Bedingung auf Gleichheit, die schnell Ergebnisse bringt, Du hast zwei Ungleichheitsbedingungen.

Die folgende "Optimierung" scheint mir fehlerhaft zu sein:

Deshalb habe ich die PHP-Datei nochmals überarbeitet:
$result_staedte = $db->query("SELECT * FROM staedte WHERE plz_von = $berater[plz]");
if (!mysql_num_rows($result_staedte))  {
$result_staedte = $db->query("SELECT * FROM staedte WHERE plz_bis = $berater[plz]");
}
if (!mysql_num_rows($result_staedte)) {
$result_staedte = $db->query("SELECT * FROM staedte WHERE $berater[plz] between plz_von and plz_bis");
}

Nun wird die BETWEEN-Anweisung erst ausgeführt, wenn die anderen zwei Anweisungen kein Ergebnis bringen. Nun habe ich 70 SQL-Abfragen mehr aber ich bekomme Ruck-Zuck ein Ergebnis.

Das gleiche wie vorher? Das gewünschte? Das bezweifle ich. Ich stelle mir vor, da gibt es eine Stadt, für die Berater von 60311 (Frankfurt) bis 66663 (Merzig) zuständig sind. Deine Vorgehensweise wird als einziges Resultat Berater aus 60311 Frankfurt liefern, nicht jedoch solche aus Merzig ...

Ein paar Ideen:
Was bringt Dir

SELECT  
    <sonstige spalten>,  
    b1.id as id,         -- Gib mir alle Berater, die für  
    s.id as stadt_id     -- eine bestimmte Stadt zuständig sind, weil  
staedte s  
INNER JOIN berater b1 ON s.plz_von <= b1.plz -- die PLZ ihres Ortes größer ist  
                                             -- als die Mindest-PLZ und  
INNER JOIN berater b2 ON s.plz_bis >= b2.plz -- kleiner als die Maximal-PLZ  
                                             -- dieser Stadt  

Wobei ich nicht erwarte, dass hier das Ergebnis viel besser ist als bei Deiner ursprünglichen Formulierung.

Weitere Idee:
Deine PHP-Schleife könntest Du (MySQL 5.x.y) in eine Stored Procedure in die DB verlagern ...

Daher meine beliebte Frage bei MySQL zum Schluß: Welche genaue Version verwendest Du? MySQL unterscheidet sich zwischen den verschiedenen Versionen enorm in seiner Leistungsfähigkeit.

Freundliche Grüße

Vinzenz

[Sven]

Hi Vinzenz, danke für die ausführliche Antwort!

Gibt es Berater, denen keine Städte zugeordnet sind?

Keinem Berater sind Städte zugeordnet :)
Ich habe ein paar Berater, bei denen eine PLZ hinterlegt ist und anhand der PLZ schaue ich nach den zugehörigen Längen-und-Breiten-Koordinaten (diese stehen in der Tabelle "staedte").

Das ist völlig logisch und normal. Du hast keine Join-Bedingung auf Gleichheit, die schnell Ergebnisse bringt, Du hast zwei Ungleichheitsbedingungen.

Ah, dann sollte ich vielleicht hier eine JOIN-Anweisung verwenden :)

Das gleiche wie vorher? Das gewünschte? Das bezweifle ich. Ich stelle mir vor, da gibt es eine Stadt, für die Berater von 60311 (Frankfurt) bis 66663 (Merzig) zuständig sind. Deine Vorgehensweise wird als einziges Resultat Berater aus 60311 Frankfurt liefern, nicht jedoch solche aus Merzig ...

Berater sind nicht für bestimmte Städte zuständig, dies ist eine Umkreissuche :) Das bedeutet, wenn du in München wohnst und es gibt einen Berater in Frankfurt und einen in Berlin wird dir der Berater in Frankfurt ausgespuckt :)

SELECT

<sonstige spalten>,
    b1.id as id,         -- Gib mir alle Berater, die für
    s.id as stadt_id     -- eine bestimmte Stadt zuständig sind, weil
staedte s
INNER JOIN berater b1 ON s.plz_von <= b1.plz -- die PLZ ihres Ortes größer ist
                                             -- als die Mindest-PLZ und
INNER JOIN berater b2 ON s.plz_bis >= b2.plz -- kleiner als die Maximal-PLZ
                                             -- dieser Stadt

  
Danke für den Code, werde ihn gleich mal ausprobieren.  
  

> Weitere Idee:  
> Deine PHP-Schleife könntest Du (MySQL 5.x.y) in eine Stored Procedure in die DB verlagern ...  
  
Leider nur MySQL - 4.0.21-standard  
  
Viele Grüße,  
Sven

[Sven Rautenberg]

Moin!

Keinem Berater sind Städte zugeordnet :)
Ich habe ein paar Berater, bei denen eine PLZ hinterlegt ist und anhand der PLZ schaue ich nach den zugehörigen Längen-und-Breiten-Koordinaten (diese stehen in der Tabelle "staedte").

Das - ist schlecht!

Wann ändert sich der geographische Ort einer Postleitzahl? Nur bei heftigen Erdbeben (beim Tsunami im indischen Ozean wurden Inseln um bis zu 30 Meter verschoben), ansonsten ist diese Angabe als konstant anzunehmen.

Was spricht also dagegen, direkt beim Eintragen der PLZ bei einem Berater direkt auch die Geo-Koordinaten aus deiner PLZ-Tabelle zu ermitteln und als Beraterdaten direkt mit abzuspeichern? Das Büro eines Beraters kann sich nur an einem Ort befinden, wenn du nur eine Adresse speicherst.

Damit beschleunigst du den Suchvorgang dann phänomenal, denn wenn du eine Umkreissuche realisierst, mußt du schließlich noch den Standort des Suchenden mit allen Standorten der existierenden Berater vergleichen, deren Entfernung ermitteln und nach Größe sortieren.

Gewiß ist diese Vorgehensweise nicht optimal hinsichtlich Redundanz - aber Redundanz ist erforderlich, wenn man mehr Geschwindigkeit haben will. Und reale Probleme lassen sich eben nicht immer in einer totnormalisierten Datenbank performant regeln.

- Sven Rautenberg

[Hamstar]

Gewiß ist diese Vorgehensweise nicht optimal hinsichtlich Redundanz - aber Redundanz ist erforderlich, wenn man mehr Geschwindigkeit haben will. Und reale Probleme lassen sich eben nicht immer in einer totnormalisierten Datenbank performant regeln.

Das Problem ist einfach, dass eine Geschäftsregel, nämlich die Zuordnung "Stadt->Berater", über das beschriebene SELECT jeweils neu angewandt wird und diese Anwendung ist so zu sagen in sich redundant. (Was man an der Ausführungsgeschwindigkeit bemerkt, es wird nämlich - da sich die Geschäftsregeln wie Du richtig bemerkst nicht oft ändern und zudem nicht die ganze Datenbasis betreffen - "immer wieder" zu viel Logik ausgeführt.)

Es spricht "exakt nichts" dagegen die Geschäftsregel - wie von uns vorgeschlagen - über Fremdschlüssel (incl. Änderungsroutine) zu implementieren. Das wäre auch auch das "Normalste auf der Welt".

Stell Dir mal eine grössere Datenbank mit vielen Tabellen vor, deren Tabellen ausschliesslich über Regeln verbunden sind, die erst beim selektierenden Datenzugriff zur Anwendung gelangen.
Also noch mal schnell ein "Würg!".   ;)

[Alexander Brock]

Hallo Forum,

SELECT *, berater.id as id, staedte.id as stadt_id FROM berater LEFT JOIN staedte ON plz_von <= berater.plz and plz_bis >=berater.plz

Du speicherst zu jedem Ort, welchen Bereich an PLZs zu ihm gehören.

Meines Erachtens wäre es effizienter, eine weitere Tabelle anzulegen, die die Felder stadt_id und plz enthält und einen Unique-Index über beide Felder zu legen, dann bräuchte man keinen Full-Table-Scan.

Gruß
Alexander Brock

[Sven]

Meines Erachtens wäre es effizienter, eine weitere Tabelle anzulegen, die die Felder stadt_id und plz enthält und einen Unique-Index über beide Felder zu legen, dann bräuchte man keinen Full-Table-Scan.

Die Idee hatte ich auch schon. Gute Idee, danke für deine Überlegungen :)

[Hamstar]

SELECT
 *,                             -- Würg, immer Datenfeldnamen angeben!
 berater.id as id,              -- Würg, warum?
 staedte.id as stadt_id         -- Würg, warum? Schreckliche Namensgebung!
FROM
 berater
LEFT JOIN
 staedte ON ((plz_von <= berater.plz) and (plz_bis >=berater.plz))
-- Würg, schrecklicher JOIN!

Du hast also die beiden Tabellen 'berater' und 'staedte' und wünscht eine Liste aller Städte und zugeordneter Berater.

Dir fällt vielleicht auf, dass dafür ein "Lauf" erforderlich ist, dass also kein Index und so vorhanden ist?

Warum? - Weil die Zuordnung "Städte->Berater" nicht im Datendesign hinterlegt ist und jeweils beim SELECT errechnet wird.

Also, schreib eine kleine Routine und befülle das neu zu erstellende Datenfeld 'Stadt_Berater_ID'. Diese ist immer auszuführen, wenn die Zuordnung "Städte->Berater" geändert wird.

Hast Du das gemacht, dann funzt es auch ganz performant mit dem SELECT.