Optimierung bei INNER JOIN mit großen Tabellen

[henning666]

Hallo,

ich habe hier ne Kunden-Tabelle mit ca. 40000 Einträgen und eine PLZ-Tabelle (vollständig, ebenfalls ca. 40000 Einträge).

Wenn man im Frontend nach Kunden in einem bestimmten Ort sucht z.B. Neustadt, und dieser Ort mehrmals innerhalb des Bundesgebietes zu finden ist z.B. Neustadt, dann wird dem User eine Select-Box angeboten in der alle Neustadts mit Landkreis und Bundesland angegeben ist. Wählt er z.B. Neustadt am Rbge. suche ich danach in der PLZ Tabelle nach allen PLZ die zu Neustadt am Rbge gehören und stelle daraus einen Query zusammen, den ich gegen die Kunden-Tabelle laufen lasse.

Query:

SELECT * FROM kunden WHERE (plz=12345 OR plz=23456 ... OR plz=34567).

Soweit so schlecht.

Sucht der User jetzt z.B. nach München (blöderweise gibt´s München und den Flughafen München Freising) wird der Query ewig lang (in Open-Office über 2 Seiten) also Query anders:

SELECT * FROM kunden AS a INNER JOIN plz_all AS b USING (plz) WHERE b.ort = 'München' AND b.kreis = 'Kreisfreie Stadt' AND b.bundesland_id = '3' LIMIT 0, 20

Super Zugriffszeit von 0,9 Sek. - alles Klar.
Von wegen:

Will der User die Datensätze umsortieren z.B. nach plz

SELECT * FROM kunden AS a INNER JOIN plz_all AS b USING (plz) WHERE b.ort = 'München' AND b.kreis = 'Kreisfreie Stadt' AND b.bundesland_id = '3' ORDER BY a.plz DESC LIMIT 0, 20

Zugriffszeit > 70 Sek = Essig.
Da ist der ewig lange Query um einiges schneller.

Hat hier jemand eine Idee??

Vielen Dank für jeden Hinweis

Henning

[diver-network]

Hi Henning,

ich gehe mal davon aus, daß Du einen sauberen Index auf a.plz UND b.plz hast. Falls nicht: Unbedingt machen.

Weiterhin ist ein SELECT * nicht die feine Art, besser ist es hier, jede Spalte einzeln aufzuführen.

Bzgl. Performance weiß ich nicht, ob das USING nicht etwas mehr kostet als ein SELECT ... ON a.plz = b.plz, eventuell auch mal an der Schraube drehen.

Schau in jedem Fall auch mal, was ein EXPLAIN SELECT ausgibt. Eventuell siehst Du daran, wenn ein Index nicht sauber verwendet werden konnte.

HTH,

Andy

[henning666]

Vielen Dank für Deine Antwort.

Ich habe bei beiden Tabellen den Index wie Empfohlen angelegt und der Query sieht jetzt so aus:

SELECT DISTINCT a.id, a.vorname, a.name, a.strasse, a.plz, a.ort, a.tel, a.bild FROM kunden AS a INNER JOIN plz_all AS b ON a.ort=b.ort WHERE b.ort = 'München' AND b.kreis = 'Kreisfreie Stadt' AND b.bundesland_id = '3' ORDER BY a.plz LIMIT 0, 20

Erklärung dazu:

table...type...possible_keys.key.key_len.ref....rows........extra
a........ref.....ort...........ort.....100.const...454...Using where; Using temporary; Using filesort
b........ref.....ort...........ort.....100.b.ort.....7

Damit kann ich leider gar nix anfangen :-(
(Wahscheinlich auch niemand anders, aber ich hab jetzt Feierabend ;-) )

Jedenfalls gab es leider keinen Performance Gewinn *schnüff*
(Allerdings läuft der ursprüngliche Query jetzt in 7 Sekunden durch *kopfkratz*, der Server war wohl noch nicht ganz wach, gestern zu lange in der Kneipe gewesen oder so ;-) )

Falls es noch weitere Ideen gibt...

Henning

p.s. kannn man in diesem Forum irgendwie HTML Tabellen darstellen??

[diver-network]

Hi Henning,

was mir als erstes auffällt ist, daß Du jetzt auf einmal mit "ort" JOINst und nicht mehr mit "plz".
Zweitens hast Du jetzt ein DISTINCT drin, was vorher nicht da stand.

Laut deinem EXPLAIN wird der Index "ort" verwendet, was bei diesem JOIN korrekt ist und auch nicht schlecht aussieht.

Setzte auf die Spalten "plz" und "ort" jeweils einen Index, damit hast Du einen Index für den JOIN und einen für den ORDER BY, so daß hier ein Performance Gewinn da sein sollte.

HTH,

Andy

[henning666]

Hi Andy,

stimmt, wo kommt denn bloss der Ort-Join her?

Nach änderung in Plz - Zugriffszeit > 0,1 Sek.

Als noch Ort drin war bekam ich ohne Distinct nur 2 Datensätze die sich ständig wiederholten *???* mit Plz geht´s auch ohne.

Super vielen Dank

da wird Cheffe sich aber freuen

Henning

[henning666]

By the Way

wo kann man denn im EXPLAIN ablesen was indexiert ist?

Ich Google mal.

Henning

[feuervogel]

vielleicht wenn du das sql direkt im mysql-client bzw im phpmyadmin ausführst?

[henning666]

Ich wollte eigentlich nur Fragen was die EXPLAIN Angaben bedeuten.
Ich habs im Manual gefunden.

Danke

Henning

[xabbuh]

Hier wird die Ausgabe von EXPLAIN erläutert.