MySQL: Verschachteltes SELECT optimieren?

[mf_2]

Hallo,

Ich habe folgendes Query (vereinfacht):

SELECT * FROM leute WHERE plz IN (SELECT plz FROM sperrliste)

Das Ding braucht Jahre, weil viele viele Zigtausende Datensätze in der sperrliste sind. Wie kann ich dem Query Beine machen? Ich meine, dass es da verschiedene Tweaks für die MySQL Abfrage gibt. Kennt ihr euch da aus?
So wie ich das als Laie sehe, vermute ich, dass das innere SELECT halt für jeden Datensatz aus leute abgearbeitet wird, was bei sagen wir 5000 Leuten und 90000 Einträgen jahrelang dauert. Kann ich das irgendwie optimieren?

Gruß,
mf_2

[Matrix316]

geht das:

select * from leute inner join sperrliste on leute.plz = sperrliste.plz

?

[mf_2]

Danke, das hat es seeehr beschleunigt!!

[Berni]

Ein Index auf plz in beiden Tabellen wäre auch noch sehr hilfreich für die Performance. Sollte bei den von dir genannten Größenverhältnissen in Bruchteilen einer Sekunde gehen.

Grundsätzlich sollte man immer seine Statements mit "Explain" analysieren um filesort oder temporäre Tabellen zu vermeiden. Des Weiteren sollten die Datentypen korrekt eingestellt werden: Korrekte Angaben der Maximallänge bei Varchar, korrekte numerische Typen (vgl. http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html ) und so Spezialkniffe wie z.B. IPs als Long speichern und nicht als Varchar (mittels inet_pton in php konvertieren). Bei Minidatenbanken mit wenig Zugriffen macht das natürlich wenig aus aber wenn mans konsequent schon beim Erstellen beachtet, gibt es eh praktisch keinen wirklichen Mehraufwand.

[Gast]

... Spezialkniffe wie z.B. IPs als Long speichern und nicht als Varchar (mittels inet_pton in php konvertieren).
nicht ip2long?
und wieso sollte das soviel mehr bringen? :|

[Berni]

Weil inet_pton auch für IPv6 geht (=>Zukunftssicherheit) im Gegensatz zu ip2long.
Das bringt deshalb mehr, weil eine Suche nach einer Long-Variable mit 4 Byte wesentlich besser geht als nach einer String-Variable mit 15 Byte. Es wird auch weniger Speicherplatz verbraucht und der Index ist kleiner und effizienter. Bei einer IPv6-Adresse stehen damit 16 Byte im Gegensatz zu sonst 39 Byte für eine Adresse (muss dann am Besten als Varbinary(16) gespeichert werden weil Bigint zu klein ist).
Bei ner kleinen Wald-und Wiesen-Applikation fürn Privatgebraucht ist das natürlich herzlich egal aber wennst mal Tabellen mit mehreren Millionen Einträgen und zahlreichen Abfragen hast, macht das schon deutlich was aus.

[Gast]

Weil inet_pton auch für IPv6 geht (=>Zukunftssicherheit) im Gegensatz zu ip2long.
Das bringt deshalb mehr, weil eine Suche nach einer Long-Variable mit 4 Byte wesentlich besser geht als nach einer String-Variable mit 15 Byte. Es wird auch weniger Speicherplatz verbraucht und der Index ist kleiner und effizienter. Bei einer IPv6-Adresse stehen damit 16 Byte im Gegensatz zu sonst 39 Byte für eine Adresse (muss dann am Besten als Varbinary(16) gespeichert werden weil Bigint zu klein ist).
Bei ner kleinen Wald-und Wiesen-Applikation fürn Privatgebraucht ist das natürlich herzlich egal aber wennst mal Tabellen mit mehreren Millionen Einträgen und zahlreichen Abfragen hast, macht das schon deutlich was aus.
aha, ok, klingt schonmal nicht schlecht.
lässt sich damit aber auch nach teilen von IPs suchen, also alle der Art 192.168.0.*?

[Berni]

Klar geht das. Du musst halt dann suchen mit sowas in der Art:
Select * from ips WHERE ip > '.inet_pton('192.168.0.0').' AND ip < '.inet_pton('192.168.0.255')
Wenn du jetzt allerdings z.B. die erste Stelle mit nem Stern versehen wirds (z.B. *.168.0.1), wirds problematisch...

[Gast]

Klar geht das. Du musst halt dann suchen mit sowas in der Art:
Select * from ips WHERE ip > '.inet_pton('192.168.0.0').' AND ip < '.inet_pton('192.168.0.255')
fein


Wenn du jetzt allerdings z.B. die erste Stelle mit nem Stern versehen wirds (z.B. *.168.0.1), wirds problematisch...
hm, wobei solche suchen ohnehin recht unüblich sind. man könnte die dann ja einfach "verbieten".

mal etwas damit rumprobieren. danke für den tipp. :)

[Shink]

Also ich würde mich die IP-to-Long-Sache nicht unbedingt trauen (viel Spaß beim Portieren nach Oracle, Suchen nach Regular Expressions oder wenn der erste auf die Idee kommt, er will da eigentlich auch Hostnames eintragen).
Mit Index sollte das doch eigentlich auch ganz fix gehen, oder?

[Berni]

viel Spaß beim Portieren nach Oracle
Oracle für ein Webprojekt mit php? Naja...Abgesehen davon erfolgt die Umwandlung ja in php und ist somit für die Datenbank vollkommen egal (solange du dort große Zahlen speichern kannst)...
Suchen nach Regular Expressions
Ich denke nicht, dass man das ernsthaft in einer Produktivdatenbank braucht. Die Performance ist indiskutabel sobald du ne größere Seite hast...ansonsten kannst du mit der Mysql-Funktion INET_NTOA auch die IP in Mysql-Abfragen umwandeln und somit ganz normal regex anwenden. Oder halt in php was aber noch langsamer sein dürfte...
oder wenn der erste auf die Idee kommt, er will da eigentlich auch Hostnames eintragen
Gut wenn du Hostnames speichern willst, ist das deine Entscheidung und du musst dann natürlich neben deinem PHP-Code auch die Datenbank anpassen (wenn man vorher Varchar(15) für die IP hergenommen hat, muss man das allerdings auch, weil ein Hostname länger sein kann). Versehentlich geschieht das aber nicht und daher sehe ich das nicht wirklich als Argument.