[Erledigt] JOIN über 3 Tabellen + 2 m:n Tabellen

dennis81 · 30.11.2010, 09:25

Hi und erstmal sorry, ich war mir jetzt nicht ganz sicher, wonach ich suchen musste, um mein Begehr zu finden. Es könnte durchaus sein, dass es hierzu im Forum bereits einen Thread gibt. Falls ja, bitte drauf hinweisen.

Zum Problem:

Ich habe in einem Auszug meiner DB drei Tabellen: Nachrichten, Empfänger und Themen. Jede Nachricht kann zu beliebig vielen Themen zugeordnet sein (m:n), genauso wie jeder Empfänger sich für beliebig viele Themen interessieren kann (m:n).
Nun möchte ich alle Nachrichten<->Empfänger Kombinationen herausfinden, die sich über die Themen verknüpfen.

Folgende Abfrage funktioniert zwar, aber ich bekomme einen Datensatz doppelt heraus. Dies könnte ich zwar durch DISTINCT beheben, aber ich bin mir nicht ganz sicher, ob dies so die korrekte Vorgehensweise ist (ich hatte auch eine Alternative, die aber zum selben Ergebnis führte):

Code:

select
  c.id consumer, m.id message, m.title
from
  ( consumer c join subject_consumer sc on c.id = sc.consumer_id )
  join
  ( message m join subject_message sm on m.id = sm.message_id )
  on
  sc.subject_id = sm.subject_id
order by
  c.id, m.id;

Die Alternative:

Code:

select
  c.id consumer, m.id message, m.title
from
  consumer c
  join (
    subject_consumer sc join (
      subject_message sm join message m on m.id = sm.message_id
    )
    on sc.subject_id = sm.subject_id
  )

  on c.id = sc.consumer_id
order by
  c.id, m.id;

Interessant sind vielleicht auch die Ausführungszeiten.

Query 1 braucht ca. 0,0041s
Query 2 braucht ca: 0,0028s

Bei natürlich gleicher Datenmende. Zukünftig werden es ein paar mehr Datensätze (min. 1.500 Empfänger), wodurch es dann auch auf die Performance ankommt.

Also, mein eigentliches Problem ist nun, ob die Vorgehensweise mit den JOINs so korrekt ist oder ob es da evtl. eine geeignetere gibt.

edit: Die redundanten Tupel im Ergebnis ergeben sich daraus, dass eine Nachricht z.B. den Themen 1,2,3,4 zugeordnet ist und ein Empfänger sich beispielsweise für 3,4 interessiert. Kann man das sinnvoll verhindern?

erc · 30.11.2010, 11:03

Die Queries sind gleich. Die Datenbank führt die Queries anders aus als du denkst.

Zitat:

Zitat von dennis81

edit: Die redundanten Tupel im Ergebnis ergeben sich daraus, dass eine Nachricht z.B. den Themen 1,2,3,4 zugeordnet ist und ein Empfänger sich beispielsweise für 3,4 interessiert. Kann man das sinnvoll verhindern?

GROUP BY oder DISTINCT

dennis81 · 30.11.2010, 13:47

So ganz gleich scheinen sie ja nicht zu sein. Der Geschwindigkeitsunterschied ist ja vorhanden. Oder meinst du, der könnte daher kommen, dass die eine Abfrage mehr Ausführungszeit vom Query Optimizer benötigt als die andere?

erc · 30.11.2010, 13:59

Zitat:

Zitat von dennis81

So ganz gleich scheinen sie ja nicht zu sein. Der Geschwindigkeitsunterschied ist ja vorhanden. Oder meinst du, der könnte daher kommen, dass die eine Abfrage mehr Ausführungszeit vom Query Optimizer benötigt als die andere?

Mach ein EXPLAIN... ich bin mir sicher das der Ausführungsplan gleich ist.

dennis81 · 30.11.2010, 15:07

Zitat:

Zitat von erc

Mach ein EXPLAIN... ich bin mir sicher das der Ausführungsplan gleich ist.

Ja, da liegst du vollkommen richtig! Auf so eine Idee sollte man selber mal kommen.

Jetzt habe ich hier aber noch eine Alternative mit Subqueries:

Code:

select
  c.id consumer,  m.id message, m.title
from
  consumer c, message m
where exists (
    select * from subject_consumer sc  join
	               subject_message  sm    on  sm.subject_id  = sc.subject_id

	 where  sc.consumer_id  = c.id    and
	        sm.message_id   = m.id

	)
order by
  c.id, m.id;

Die ist auf dem ersten "Blick" schneller. EXPLAIN zeigt auch etwas anderes (kein Wunder...).
Hier habe ich im zweiten Schritt aber ein "Using where, Using join buffer" mit 14 rows. In den zuerst genannten Beispielen ist es jeweils "Using Index" mit 2 rows.
Was sagt mir das? Ich habe mich noch nie wirklich mit EXPLAIN & Co auseinander gesetzt. Um ehrlich zu sein wundert es mich ein wenig, dass die zuletzt gezeigte Lösung die schnellste ist (bei dieser geringen Datenmenge). Das ist allerdings eine naive, ahnungslose Verwunderung.

erc · 30.11.2010, 16:20

Zitat:

Zitat von dennis81

Die ist auf dem ersten "Blick" schneller. EXPLAIN zeigt auch etwas anderes (kein Wunder...).

Aber nur auf den Ersten. Der Query ist ganz ganz ganz schlecht, mega schlecht. Die Ausführungszeit steigt exponential. Erstens du hast ein dependet Subquery (sagt dir auch EXPLAIN), das heißt dieser wird für jeden Datensatz ausgeführt. Schlecht. Zweitens du hast ein JOIN ohne Bedigung. Mega schlecht! Das bildet dir ein Kreuzprodukt zwischen consumer u. message. Und für jeden Datensatz in diesem Kreuzprodukt wird der Subquery ausgeführt.

Die Variante mit den JOINs ist schon in Ordnung.

dennis81 · 01.12.2010, 09:07

Zitat:

Zitat von erc

Die Variante mit den JOINs ist schon in Ordnung.

Alles klar. Vielen Dank für die Hinweise!

dennis81 · 08.12.2010, 15:36

Nachtrag:

Wie ich ja erwähnt hatte, sollte noch eine zusätzliche Verknüfung mit ins Spiel kommen. Mein Query lautet nun:

Code:

select distinct
                  c.id consumer, m.id message
                from
                  ( consumer c
                    join subject_consumer sc on c.id = sc.consumer_id
                    join location_consumer lc on c.id = lc.consumer_id
                  )
                  join
                  ( message m
                      join subject_message sm on m.id = sm.message_id
                      join location_message lm on m.id = lm.message_id
                  )
                  on
                  sc.subject_id = sm.subject_id and lc.location_id = lm.location_id
                order by
                  c.id, m.id;

Gegenüber der "where exists"-Variante ist die Lösung erheblich schneller (vgl. Angaben aus dem Query-Browser).

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
Abfrage über drei Tabellen	michu	Datenbanken	5	11.10.2010 22:43
[Erledigt] Left Join 3 Tabellen	mykosch	Datenbanken	5	16.09.2010 14:51
Query über mehrere Tabellen	ph\|L	Datenbanken	3	07.04.2009 14:26
Persistence Framework	#Avedo	Software-Design	37	28.03.2009 17:32
[Erledigt] Join über 3 Tabellen	BlackPanther	Datenbanken	2	11.03.2009 19:56
SELECT über zwei Tabellen	KingCrunch	Datenbanken	9	23.03.2007 04:01
Update über zwei Tabellen	tekknotrip	Datenbanken	2	26.02.2007 16:25
Abfrage über 3 Tabellen	madSoul	Datenbanken	5	26.06.2006 12:25
SQL ABfrage über mehrere Tabellen	Madden	Datenbanken	17	09.05.2006 21:19
Select über 2 Tabellen	MasterMind	Datenbanken	11	08.04.2006 11:43
Select über mehrere tabellen	Sclot	Datenbanken	14	24.11.2005 16:56
[Erledigt] Join mit 3 Tabellen		Datenbanken	21	16.05.2005 23:20
sql über 2 tabellen		PHP Tipps 2005	2	25.04.2005 21:26
Abfrage über 3 Tabellen		PHP Tipps 2004	3	31.07.2004 22:34
[Erledigt] Join für Select Count(`id`)-Abfrage über 4 Tabellen		Datenbanken	4	10.07.2004 18:32

30.11.2010, 09:25
dennis81 Erfahrener Benutzer Registriert seit: 13.05.2009 Beiträge: 1.164 PHP-Kenntnisse: Fortgeschritten	[Erledigt] JOIN über 3 Tabellen + 2 m:n Tabellen Hi und erstmal sorry, ich war mir jetzt nicht ganz sicher, wonach ich suchen musste, um mein Begehr zu finden. Es könnte durchaus sein, dass es hierzu im Forum bereits einen Thread gibt. Falls ja, bitte drauf hinweisen. Zum Problem: Ich habe in einem Auszug meiner DB drei Tabellen: Nachrichten, Empfänger und Themen. Jede Nachricht kann zu beliebig vielen Themen zugeordnet sein (m:n), genauso wie jeder Empfänger sich für beliebig viele Themen interessieren kann (m:n). Nun möchte ich alle Nachrichten<->Empfänger Kombinationen herausfinden, die sich über die Themen verknüpfen. Folgende Abfrage funktioniert zwar, aber ich bekomme einen Datensatz doppelt heraus. Dies könnte ich zwar durch DISTINCT beheben, aber ich bin mir nicht ganz sicher, ob dies so die korrekte Vorgehensweise ist (ich hatte auch eine Alternative, die aber zum selben Ergebnis führte): Code: select c.id consumer, m.id message, m.title from ( consumer c join subject_consumer sc on c.id = sc.consumer_id ) join ( message m join subject_message sm on m.id = sm.message_id ) on sc.subject_id = sm.subject_id order by c.id, m.id; Die Alternative: Code: select c.id consumer, m.id message, m.title from consumer c join ( subject_consumer sc join ( subject_message sm join message m on m.id = sm.message_id ) on sc.subject_id = sm.subject_id ) on c.id = sc.consumer_id order by c.id, m.id; Interessant sind vielleicht auch die Ausführungszeiten. Query 1 braucht ca. 0,0041s Query 2 braucht ca: 0,0028s Bei natürlich gleicher Datenmende. Zukünftig werden es ein paar mehr Datensätze (min. 1.500 Empfänger), wodurch es dann auch auf die Performance ankommt. Also, mein eigentliches Problem ist nun, ob die Vorgehensweise mit den JOINs so korrekt ist oder ob es da evtl. eine geeignetere gibt. edit: Die redundanten Tupel im Ergebnis ergeben sich daraus, dass eine Nachricht z.B. den Themen 1,2,3,4 zugeordnet ist und ein Empfänger sich beispielsweise für 3,4 interessiert. Kann man das sinnvoll verhindern? __________________ Es ist schon alles gesagt! Nur noch nicht von allen! (Karl Valentin) Wenn du eine weise Antwort verlangst, musst du vernünftig fragen. (Johann Wolfgang von Goethe) Geändert von dennis81 (30.11.2010 um 10:08 Uhr).


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

30.11.2010, 13:47
dennis81 Erfahrener Benutzer Registriert seit: 13.05.2009 Beiträge: 1.164 PHP-Kenntnisse: Fortgeschritten	So ganz gleich scheinen sie ja nicht zu sein. Der Geschwindigkeitsunterschied ist ja vorhanden. Oder meinst du, der könnte daher kommen, dass die eine Abfrage mehr Ausführungszeit vom Query Optimizer benötigt als die andere? __________________ Es ist schon alles gesagt! Nur noch nicht von allen! (Karl Valentin) Wenn du eine weise Antwort verlangst, musst du vernünftig fragen. (Johann Wolfgang von Goethe)

08.12.2010, 15:36
dennis81 Erfahrener Benutzer Registriert seit: 13.05.2009 Beiträge: 1.164 PHP-Kenntnisse: Fortgeschritten	Nachtrag: Wie ich ja erwähnt hatte, sollte noch eine zusätzliche Verknüfung mit ins Spiel kommen. Mein Query lautet nun: Code: select distinct c.id consumer, m.id message from ( consumer c join subject_consumer sc on c.id = sc.consumer_id join location_consumer lc on c.id = lc.consumer_id ) join ( message m join subject_message sm on m.id = sm.message_id join location_message lm on m.id = lm.message_id ) on sc.subject_id = sm.subject_id and lc.location_id = lm.location_id order by c.id, m.id; Gegenüber der "where exists"-Variante ist die Lösung erheblich schneller (vgl. Angaben aus dem Query-Browser). __________________ Es ist schon alles gesagt! Nur noch nicht von allen! (Karl Valentin) Wenn du eine weise Antwort verlangst, musst du vernünftig fragen. (Johann Wolfgang von Goethe)