[Erledigt] Select * where in (Select ...) Geschwindigkeitsproblem

Wolla · 19.05.2009, 15:00

Ich stehe grade vollkommen neben der Mütze. Was mache ich falsch?

Ich habe eine Tabelle mit Adressen, darin auch eine Spalte `plz`.
Ich habe eine Tabelle mit 300 Postleitzahlen. Jede Zeile hat nur eine Spalte `suchplz`auf der ein key liegt.

ich möchte nun alle Adressen selektieren, bei denen die PLZ in der Suchtabelle vorkommt.

Das hier habe ich in PHPmyadmin versucht:

SELECT * FROM adressen
WHERE plz IN (SELECT suchplz FROM plzliste)

Das läuft auf einen Timeout - offensichtlich baue ich da ein kartesisches Produkt aus beiden Tabellen auf (800.000 mal 300) oder mache anderen Unsinn.

Dann habe ich das versucht:

SELECT * FROM adressen
LEFT JOIN plzliste
ON plz=suchplz

aber das läuft ebenfalls auf einen Timeout

Explain bringt mich nicht wirklich weiter. Das sagt nur, dass der Key in der PLZ-Tabelle benutzt wird.

Hat mal jemand nen Tipp oder Schraubenzieher parat?

(PS: natürlich mache ich original nicht "SELECT *")

erc · 19.05.2009, 16:23

Vielleicht Postest du einfach mal das EXPLAIN!? Und wieso LEFT JOIN wenn du eh nur die Datensätze haben willst die in Tabelle 2 vorkommen?

Wolla · 19.05.2009, 16:46

Code:

EXPLAIN SELECT *
FROM adressen
WHERE plz
IN (

SELECT suchplz
FROM such_plz
)

Code:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	adressen 	ALL 	NULL 	NULL 	NULL 	NULL 	872696 	Using where
2 	DEPENDENT SUBQUERY 	such_plz 	index 	NULL 	suchplz 	[BINARY - 1Bytes] 	NULL 	494 	Using where; Using index

Wenn ich eine kleine Liste PLZ mit in die Query tippe, läuft es höllisch schnell:

SELECT * FROM adressen WHERE plz IN (
'12345',
'12346',
'12347'
)

erc · 19.05.2009, 16:56

Ich wollt das EXPLAIN von der JOIN Variante. Die Idee mit dem Subquery würd ich mir aus den Kopf schlagen. Mysql führt den Subquery für jeden Datensatz aus... sprich 800.000 mal.

Wolla · 19.05.2009, 17:30

Code:

EXPLAIN SELECT *
FROM adressen
LEFT JOIN such_plz ON plz = suchplz


id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	adressen 	ALL 	NULL 	NULL 	NULL 	NULL 	872696 	 
1 	SIMPLE 	such_plz 	index 	NULL 	suchplz 	[BINARY - 1Bytes] 	NULL 	494 	Using index

Hier fehlt wohl noch ein WHERE-Teil, aber ich hatte da eh schon die nase voll und war auf dem Weg ins Bett.

Chriz · 19.05.2009, 20:47

Setz einen Index auf die PLZ in Adressen.

Wolla · 19.05.2009, 21:20

Die Tabelle hat auf PLZ einen Index.

Code:

Name	Typ	Unique	Packed	Feld	Kardinalität	Kollation
plz	BTREE	Nein	Nein	plz	22965	A

Muss man den Index im Select - Statement erzwingen?

Chriz · 19.05.2009, 21:36

Wenn auf adressen.plz und such_plz.suchplz ein Index liegt, hast du praktisch keine Möglichkeit mehr die Geschwindigkeit zu erhöhen.

Du kannst höchstens verschiedene Queries testen:
SELECT * FROM adressen WHERE plz IN (SELECT suchplz FROM such_plz)

SELECT a.* FROM such_plz AS s
LEFT JOIN adressen AS a
ON a.plz = s.suchplz

SELECT a.* FROM adressen AS a
INNER JOIN such_plz AS S
ON s.suchplz = a.plz

Was ist denn suchplz überhaupt? Kannst du das Ergebnis cachen? Ich bezweifle eigentlich, dass das für MySQL und damit auch PHP mit dem Timeout ein Problem ist ..

Wolla · 19.05.2009, 21:43

Ich will halt die Kunden für eine Menge an PLZ zusammenfassen. In such_plz ist die Liste der PLZ-Gebiete, die besonders betrachtet werden sollen.

Die Liste ist halt zu groß, um ... WHERE plz ='12345' or plz='12346' or ... or '34567' schreiben zu können.

Anscheinend stoße ich hier an physikalische Grenzen. Dann muss ich das anders lösen.

Chriz · 20.05.2009, 00:31

Nein tust du nicht, irgendwas stimmt mit deiner Struktur nicht, einen Timeout kann ich mir nicht vorstellen.

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
mehrere select count zusammen fassen	reddevil82	PHP Tipps 2009	9	03.05.2009 21:45
[Erledigt] Select Where IN liefert nur einen Teil der Ergebnisse	williwutz2008	Datenbanken	8	10.11.2008 19:43
SELECT ... WHERE ... Nur ein User!	Mike²	Datenbanken	1	30.09.2006 19:15
SELECT WHERE datetime abfrage - Optimierung	mrSpok	Datenbanken	15	23.03.2006 19:08
[Erledigt] select where TIME		Datenbanken	7	17.02.2006 12:40
SELECT FROM WHERE..AND...OR		PHP Tipps 2006	4	14.02.2006 09:59
SELECT und WHERE: alles außer		PHP Tipps 2005-2	5	08.10.2005 12:47
Select * FROM * WHERE ???		PHP Tipps 2005-2	12	07.10.2005 17:45
where -x- in (select...) klappt nicht	Unbekanntes_Pferd	Datenbanken	3	16.07.2005 14:36
Befehlsoptimierung	faux	Datenbanken	4	31.05.2005 19:11
SELECT * FROM tabelle WHERE	DarkThunder	PHP Tipps 2005	10	23.04.2005 21:17
SELECT FROM DB WHERE 2 SACHEN UEBEREINSTIMMEN		Datenbanken	3	24.01.2005 15:58
SELECT FROM...WHERE id=(SELECT...)	bicpi	Datenbanken	4	24.10.2004 10:53
[Erledigt] SELECT Where id != 17 und nochwas.....		Datenbanken	4	13.09.2004 15:22
[Erledigt] select ALLES from EGALWOHER where WASAUCHIMMER		Datenbanken	1	01.06.2004 11:29

19.05.2009, 15:00
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 4.945 PHP-Kenntnisse: Fortgeschritten	*[Erledigt] Select where in (Select ...) Geschwindigkeitsproblem** Ich stehe grade vollkommen neben der Mütze. Was mache ich falsch? Ich habe eine Tabelle mit Adressen, darin auch eine Spalte `plz`. Ich habe eine Tabelle mit 300 Postleitzahlen. Jede Zeile hat nur eine Spalte `suchplz`auf der ein key liegt. ich möchte nun alle Adressen selektieren, bei denen die PLZ in der Suchtabelle vorkommt. Das hier habe ich in PHPmyadmin versucht: SELECT * FROM adressen WHERE plz IN (SELECT suchplz FROM plzliste) Das läuft auf einen Timeout - offensichtlich baue ich da ein kartesisches Produkt aus beiden Tabellen auf (800.000 mal 300) oder mache anderen Unsinn. Dann habe ich das versucht: SELECT * FROM adressen LEFT JOIN plzliste ON plz=suchplz aber das läuft ebenfalls auf einen Timeout Explain bringt mich nicht wirklich weiter. Das sagt nur, dass der Key in der PLZ-Tabelle benutzt wird. Hat mal jemand nen Tipp oder Schraubenzieher parat? (PS: natürlich mache ich original nicht "SELECT *")


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

19.05.2009, 16:23
erc Erfahrener Benutzer Registriert seit: 02.01.2009 Beiträge: 730 PHP-Kenntnisse: Fortgeschritten	Vielleicht Postest du einfach mal das EXPLAIN!? Und wieso LEFT JOIN wenn du eh nur die Datensätze haben willst die in Tabelle 2 vorkommen?

19.05.2009, 16:46
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 4.945 PHP-Kenntnisse: Fortgeschritten	Code: EXPLAIN SELECT * FROM adressen WHERE plz IN ( SELECT suchplz FROM such_plz ) Code: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY adressen ALL NULL NULL NULL NULL 872696 Using where 2 DEPENDENT SUBQUERY such_plz index NULL suchplz [BINARY - 1Bytes] NULL 494 Using where; Using index Wenn ich eine kleine Liste PLZ mit in die Query tippe, läuft es höllisch schnell: SELECT * FROM adressen WHERE plz IN ( '12345', '12346', '12347' ) Geändert von Wolla (19.05.2009 um 16:51 Uhr).

19.05.2009, 16:56
erc Erfahrener Benutzer Registriert seit: 02.01.2009 Beiträge: 730 PHP-Kenntnisse: Fortgeschritten	Ich wollt das EXPLAIN von der JOIN Variante. Die Idee mit dem Subquery würd ich mir aus den Kopf schlagen. Mysql führt den Subquery für jeden Datensatz aus... sprich 800.000 mal.

19.05.2009, 20:47
Chriz Moderator Registriert seit: 11.05.2008 Beiträge: 6.266	Setz einen Index auf die PLZ in Adressen. __________________ "Nuschel ich?" - "Was?"

19.05.2009, 21:20
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 4.945 PHP-Kenntnisse: Fortgeschritten	Die Tabelle hat auf PLZ einen Index. Code: Name Typ Unique Packed Feld Kardinalität Kollation plz BTREE Nein Nein plz 22965 A Muss man den Index im Select - Statement erzwingen?

19.05.2009, 21:36
Chriz Moderator Registriert seit: 11.05.2008 Beiträge: 6.266	Wenn auf adressen.plz und such_plz.suchplz ein Index liegt, hast du praktisch keine Möglichkeit mehr die Geschwindigkeit zu erhöhen. Du kannst höchstens verschiedene Queries testen: SELECT * FROM adressen WHERE plz IN (SELECT suchplz FROM such_plz) SELECT a.* FROM such_plz AS s LEFT JOIN adressen AS a ON a.plz = s.suchplz SELECT a.* FROM adressen AS a INNER JOIN such_plz AS S ON s.suchplz = a.plz Was ist denn suchplz überhaupt? Kannst du das Ergebnis cachen? Ich bezweifle eigentlich, dass das für MySQL und damit auch PHP mit dem Timeout ein Problem ist .. __________________ "Nuschel ich?" - "Was?"

19.05.2009, 21:43
Wolla Moderator Registriert seit: 06.06.2008 Beiträge: 4.945 PHP-Kenntnisse: Fortgeschritten	Ich will halt die Kunden für eine Menge an PLZ zusammenfassen. In such_plz ist die Liste der PLZ-Gebiete, die besonders betrachtet werden sollen. Die Liste ist halt zu groß, um ... WHERE plz ='12345' or plz='12346' or ... or '34567' schreiben zu können. Anscheinend stoße ich hier an physikalische Grenzen. Dann muss ich das anders lösen.

20.05.2009, 00:31
Chriz Moderator Registriert seit: 11.05.2008 Beiträge: 6.266	Nein tust du nicht, irgendwas stimmt mit deiner Struktur nicht, einen Timeout kann ich mir nicht vorstellen. __________________ "Nuschel ich?" - "Was?"