[MYSQL] Verständnissfrage zur WHERE Klausel

[boyben]

CREATE TEMPORARY TABLE tmp2_400 SELECT * FROM `t_400` WHERE `lfdnr` = '7254' AND artnr = '5020401 ';
SELECT t_030_1.bez AS Kriterium, t_030_2.bez AS kritbez 
FROM tmp2_400 
LEFT JOIN t_050 ON tmp2_400.kritnr = t_050.kritnr 
LEFT JOIN t_030 AS t_030_1 ON t_050.beznr = t_030_1.beznr 
LEFT JOIN t_052 ON tmp2_400.kritwert = CONVERT( t_052.`key` , signed ) 
LEFT JOIN t_030 AS t_030_2 ON t_052.beznr = t_030_2.beznr WHERE (t_050.tabnr = t_052.tabnr) AND t_030_1.sprachnr = '1' AND t_030_2.sprachnr = '1' 
GROUP BY t_030_2.bez 
ORDER BY t_030_1.bez

Die temporäre Tabelle oben erstelle ich nur weil anscheinend zu doof bin die WHERE Klausel korrekt zu formulieren!
Normal wäre ja auch das hier machbar!

SELECT t_030_1.bez AS Kriterium, t_030_2.bez AS kritbez 
FROM t_400 LEFT JOIN t_050 ON t_400.kritnr = t_050.kritnr 
LEFT JOIN t_030 AS t_030_1 ON t_050.beznr = t_030_1.beznr 
LEFT JOIN t_052 ON t_400.kritwert = CONVERT( t_052.`key` , signed ) 
LEFT JOIN t_030 AS t_030_2 ON t_052.beznr = t_030_2.beznr 
WHERE (t_050.tabnr = t_052.tabnr) AND t_030_1.sprachnr = '1' AND t_030_2.sprachnr = '1' AND t_400.`lfdnr` = '7254' AND t_400.artnr = '5020401' GROUP BY t_030_2.bez ORDER BY t_030_1.bez

Nur wenn ich mir dann per EXPLAIN anzeigen lasse wie der MYSQL Server vorgeht, dann kommt mir das schaudern!
Hier mal ein Dump davon:

1  	SIMPLE  	t_030_1  	ref  	sprachnr,beznr  	sprachnr  	4  	const  	16731  	Using where; Using temporary; Using filesort
1 	SIMPLE 	t_050 	ref 	beznr 	beznr 	4 	tecdoc.t_030_1.beznr 	12 	Using where
1 	SIMPLE 	t_052 	ref 	tabnr,beznr 	tabnr 	4 	tecdoc.t_050.tabnr 	12 	Using where
1 	SIMPLE 	t_030_2 	ref 	sprachnr,beznr 	beznr 	4 	tecdoc.t_052.beznr 	73 	Using where
1 	SIMPLE 	t_400 	ref 	kritwert,kritnr 	kritnr 	4 	tecdoc.t_050.kritnr 	681 	Using where

Bei der t_400 zeigt er mit also 681 Datensätze an also zieht die WHERE Klausel nicht, denn wenn ich schreibe

SELECT * FROM t_400 WHERE artnr = '5020401' and lfdnr='7254'

dann erhalte ich 3 Datensätze. Also ist es mir total schleierhaft wieso der gegen 681 Datensätze joint!
Jemand ne Idee oder sieht auf Anhieb ob die WHERE Klausel verkehrt ist?

LG
Bebo

 

[lmarkus31]

Hallo,

das was du im EXPLAIN siehst, ist nur das Vorgehen der Datenbank. Deine WHERE-Klausel ist durchaus korrekt.
Das was du gesehn hast, bedeutet, dass das DBMS aus der Teilabfrage / Tabelle voraussichtlich 681 Datensätze identifiziert hat, die es nach deinen Kriterien durchsuchen muss, um zu einem Ergebnis zu kommen.
Bei Vorhandensein und -Nutzung eines bestimmten Indizes zur Einschränkung müssen eventuell weniger Datensätze durchsucht werden.
Wieviele das sind, hängt von der Kardinalität der Tabellenspalte im Index ab. (die sieht man aber auch bei SHOW INDEX FROM <table>)

Kannst dieses Verhalten ja gerne mal ausprobieren indem du einfach eine neue Tabelle mit Primärschlüssel anlegst, einige Datensätze mit jeweils eindeutiger ID anlegest und eine Query gegen eine einzelne ID machst. (... WHERE id = ...)
Wenn du dann ein EXPLAIN laufen lässt, siehst du bei Rows eine 1, da der Index benutzt wird und die ID eindeutig ist, also nur einen Datensatz kennzeichnet.

Wenn du dagegen mal machst: ALTER TABLE <table> DROP PRIMARY KEY
und deine Abfrage nochmal ausführst, wirst du sehen, dass sie immer noch einen Datensatz liefert, das EXPLAIN dagegen dir bei Rows eine der Anzahl der Tabelle entsprechende Zahl anzeigt.

Markus

 

[boyben]

Moment wie beinflusst ein Primary Key die Abfrage? Ich dachte immer nur das ein Index auf der Spalte die ich Joine Geschwindigkeitsvorteile bringt!
Den momentan gibt es in meiner Datenbank keine Primärschlüssel!

 

[lmarkus31]

Hallo nochmal,

weil das Anlegen eines Primary Key normalerweise automatisch einen PRIMARY und damit UNIQUE INDEX auf der Spalte erzeugt. Zumindest bei allen mir bekannten RDBMS:
Ausserdem war das, was ich beschrieben hab nur für das Beispiel zum Ausprobieren gedacht. Ich hab das nicht auf deine schon existierenden Tabellen bezogen.

Wenn deine Abfrage also keine Indizes benutzt, sind die Zahlen, die bei Dir bei Rows angegeben sind, in Ordnung.

Markus

 

[boyben]

Hmm ich hatte auf artnr und lfdnr kein INDEX jezt geht die Abfrage in 0,8 Sekunden vorher dauerte Sie 18 Sekunden !
Danke für den Hinweis lmarkus31!