PHP Forum

PHP Forum (http://www.selfphp.de/forum/index.php)
-   MySQLi/PDO/(MySQL) (http://www.selfphp.de/forum/forumdisplay.php?f=22)
-   -   Optimierung bei INNER JOIN mit großen Tabellen (http://www.selfphp.de/forum/showthread.php?t=11061)

henning666 26.07.2005 15:35:07

Optimierung bei INNER JOIN mit großen Tabellen
 
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:
Code:

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:
Code:

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
Code:

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 26.07.2005 18:15:56

AW: Optimierung bei INNER JOIN mit großen Tabellen
 
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 26.07.2005 19:04:33

AW: Optimierung bei INNER JOIN mit großen Tabellen
 
Vielen Dank für Deine Antwort.

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

Code:

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 27.07.2005 10:13:27

AW: Optimierung bei INNER JOIN mit großen Tabellen
 
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 27.07.2005 11:55:35

AW: Optimierung bei INNER JOIN mit großen Tabellen
 
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 27.07.2005 11:58:14

AW: Optimierung bei INNER JOIN mit großen Tabellen
 
By the Way

wo kann man denn im EXPLAIN ablesen was indexiert ist?

Ich Google mal.

Henning

feuervogel 27.07.2005 12:26:00

AW: Optimierung bei INNER JOIN mit großen Tabellen
 
vielleicht wenn du das sql direkt im mysql-client bzw im phpmyadmin ausführst?

henning666 27.07.2005 13:51:13

AW: Optimierung bei INNER JOIN mit großen Tabellen
 
Ich wollte eigentlich nur Fragen was die EXPLAIN Angaben bedeuten.
Ich habs im Manual gefunden.

Danke

Henning

xabbuh 27.07.2005 14:42:25

AW: Optimierung bei INNER JOIN mit großen Tabellen
 
Hier wird die Ausgabe von EXPLAIN erläutert.


Alle Zeitangaben in WEZ +2. Es ist jetzt 13:32:51 Uhr.

Powered by vBulletin® Version 3.8.3 (Deutsch)
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.