komplexere Datenbankabfrage (MySQL)

[lonely_hawk]

wie der Titel schon sagt, versuche ich mich gerade an einer Datenbankabfrage. Folgendes:

Ich habe folgenden Tabellenaufbau:

tab1 zwei Spalten

bez_id | bezeichnung

tab2 zwei Spalten

bez_id | info_id

tab3 zwei Spalten

info_id | Wert

nun hätte ich gern eine liste aller 'bezeichnungen' (tab1) wo info_id(tab2) = info_id(tab3)

Mein derzeitiger Ansatz sieht so aus:

SELECT t1.bez_id FROM tab1 t1, tab2 t2, tab3 t3 WHERE t1.bez_id = t2.bez_id AND t2.info_id = '$ein_wert'

für $ein_wert wird natürlich dann ein konkreter Wert eingesetzt. Die Abfrage funktioniert, liefert aber mist... nämlich eine Tabelle, deren Anzal an Zeilen der Anzahl aller Einträge der Tabelle tab1 entsprechen und Bezeichungen und Werte zusammensetzt, die definitiv nicht stimmen. Aber ich habe keine Ideen mehr, woran es liegt.

Jemand eine Idee?

derzeit Löse ich das Problem mit zwei einzelnen Selectabfragen

1. bez_id where info_id = '$ein_wert'
2. bezeichung where bez_id

 

[ein schlauer]

Vielleicht so?

SELECT .... FROM tab1 LEFT JOIN tab2 USING(bez_id) LEFT JOIN tab3 USING(info_id) WHERE tab2.info_id = '$ein_wert';

 

[lonely_hawk]

Vielen Dank. So funktioniert es.

Dann werd ich mal weiter basteln...

Vielleicht so?

SELECT .... FROM tab1 LEFT JOIN tab2 USING(bez_id) LEFT JOIN tab3 USING(info_id) WHERE tab2.info_id = '$ein_wert';

 

[lonely_hawk]

ok wie geschrieben die lösung funktioniert erstmal.

wie stelle ich es jetzt am besten an, wenn ich nicht nur 'bezeichnungen' aus tab1 auslesen möchte sondern zusetzlich auch noch 'Wert' aus tab3 in der selben selectabfrage?

 

[ein schlauer]

SELECT tab3.wert,.....

 

[Haerti]

Es geht auch einfach mit:

SELECT t1.bezeichnung, t3.wert 
FROM tab1 t1, tab2 t2, tab3 t3 
WHERE t1.bez_id = t2.bez_id 
and t2.info_id = t3.info_id
and t2.info_id = '$ein_wert';

dann braucht man das Left Join Using nicht.

 

[ein schlauer]

Was heißt braucht nicht? Ist deine Variante schneller oder sonstwie besser?

 

[Haerti]

Ansich kommt das gleich dabei raus ich find es nur leichter zu merken.

Aber das kann man ja machen wie man will.
Das:

dann braucht man das Left Join Using nicht.

war nur rein auf den Befehl bezogen.

 

[ein schlauer]

Ansich kommt das gleich dabei raus ich find es nur leichter zu merken.

Aber das kann man ja machen wie man will.
Das:

war nur rein auf den Befehl bezogen.

Ich hab hier kein mysql, aber was sagt den Explain? Meiner Meinung dürfte der left join wesentlich Effektiver sein.

 

[lonely_hawk]

vielen dank nochmal! :icon7:

es geht auch noch einfacher...

SELECT wert, bezeichnung FROM tab1 LEFT JOIN tab2 USING(bez_id) LEFT JOIN tab3 USING(info_id) WHERE ....

also ohne zu kennzeichnen aus welcher tabelle der 'wert' kommt.
sollte dann nur keine dopplung der Splatennamen auftreten...

SELECT tab3.wert,.....

 

[Iltis]

Es geht auch einfach mit:

SELECT t1.bezeichnung, t3.wert 
FROM tab1 t1, tab2 t2, tab3 t3 
WHERE t1.bez_id = t2.bez_id 
and t2.info_id = t3.info_id
and t2.info_id = '$ein_wert';

dann braucht man das Left Join Using nicht.

Diese Variante ist unbedingt zu vermeiden!

Wenn du die Abfrage so aufbaust, bildet MySQL das kartesische Produkt dieser 3 Tabellen. Das heisst jede Zeile von jeder Tabelle wird mit jeder Zeile der anderen beiden Tabellen zusammengeführt. Es entsteht also eine riesige Tabelle. Daraus werden dann die Zeilen ausgewählt, bei denen die WHERE-Anweisung zutrifft.

MySQL kann alle Joins massiv schneller abarbeiten.

Der Unterschied zwischen den beiden Methoden merkt man vor allem bei grösseren Tabellen.

 

[Haerti]

Oh ok, man lernt nie aus, danke für die Info

 

[ein schlauer]

Diese Info hätte dir vermutlich auch- das schon von mir erwähnte - Explain gegeben und so oder so wichtiger noch sind die richtigen Indizes auf die Felder mit denen du verknüpfst und nach denen du suchst.

 

[ZeitGeist]

Der Join ist wichtiger. Das kartesische Produkt führt zu Laufzeitverhalten von m*n.

 

[ein schlauer]

Ich hab das jetzt mal ausprobiert, mit jeweils 20,000 Datensätzen. Ein Index auf die zwei IDs gesetzt, tab2 hat keinen.

Explain sagt folgendes:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	tab2 	ALL 	NULL 	NULL 	NULL 	NULL 	1001 	Using where
1 	SIMPLE 	tab1 	eq_ref 	bez_id 	bez_id 	4 	test.tab2.bez_id 	1 	 
1 	SIMPLE 	tab3 	eq_ref 	info_id 	info_id 	4 	test.tab2.info_id 	1

Ohne Join

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	t2 	ALL 	NULL 	NULL 	NULL 	NULL 	1001 	Using where
1 	SIMPLE 	t1 	eq_ref 	bez_id 	bez_id 	4 	test.t2.bez_id 	1 	 
1 	SIMPLE 	t3 	eq_ref 	info_id 	info_id 	4 	test.t2.info_id 	1 	Using where

Mit join.

Beide Abfragen sind also fast identisch, es wird also kein kreuzprodukt gebildet (was mich auch ein bisschen verwundert) und auch von der Geschwindigkeit kein Unterschied.

 

[ZeitGeist]

Da hat der Anfragenoptimierer das erkannt und nen Join daraus gemacht