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). 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 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 Da ist der ewig lange Query um einiges schneller. Hat hier jemand eine Idee?? Vielen Dank für jeden Hinweis Henning |
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 |
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 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?? |
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 |
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 |
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 |
AW: Optimierung bei INNER JOIN mit großen Tabellen
vielleicht wenn du das sql direkt im mysql-client bzw im phpmyadmin ausführst?
|
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 |
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.