exkludierender INNER JOIN

pepe24 · 17.12.2007, 12:36

Hallo,

habe ein kleines JOIN-Problem.
Es handelt sich hier um zwei Tabellen - wobei Tabelle 2 mit Daten aus
Tabelle 1 befüllt wird. Das ganze sieht folgendermaßen aus:

Table1: (ca. 10k Datensätze)
s_id | hip_nr | details | ....

Table2:
t_id | hip_nr | prefix | ....

Bisher sieht die Abfrage so (in etwa) aus:

PHP-Code:

  SELECT t1.s_id, t1.hip_nr, t1.details ... FROM table1 as t1 INNER JOIN table2 as t2 on(t1.hip_nr != t2.hip_nr) WHERE prefix = 'A' ORDER BY rand() LIMIT 1;

Erklärung "prefix": Beim Schreiben in t2 wird ein Prefix gesetzt, sinn
dahinter ist, dass man t1 wieder von vorne in t2 eintragen kann, nur mit
einem anderen Prefix. (mehrere Cycles); Sollte bei einer Erklärung aber
unberücksichtigt bleiben, denn es macht die Sache unnötig kompliziert.

Sinn an der ganzen Sache ist, dass mir die Abfrage heraussucht:
- welche hip_nr aus t1 steht noch nicht in t2

Problem mit meiner Abfrage ist weniger, dass sie nicht die richtigen
Ergebnisse liefert (das tut sie), sondern mehr, dass sie über 4 Sekunden
dafür braucht, was einfach nicht tragbar ist.

Danke für Eure Mühe.

Gruß,
Peter

cycap · 17.12.2007, 12:49

Sowas? (Bin mir nicht sicher ob ich dich verstanden hab)

Code:

SELECT... FROM table1 AS t1 LEFT JOIN table 2 AS t2 ON t1.hip_nr = t2.hip_n WHERE t2.hip_nr IS NULL

Zergling-new · 17.12.2007, 12:53

Hallo,
wenn die Abfrage solange dauert, fehlen vielleicht die Indexe: Die sollten für alle Verknüpfungsfelder und alle Bedingungen gesetzt werden.

Vielleicht ist "Prefix" als Stringfeld auch nicht so geeignet. Läßt sich das ganze auf numerische Werte umstellen?

Zieh mal das prefix = 'A' in die Join-Bedingung (ON condition1 AND condition2), vielleicht ist das schneller, weil die Restriktion früher in Kraft tritt und somit weniger Zwischenergebnisse anfallen (wobei das auch nur eine Vermutung ist).

pepe24 · 17.12.2007, 13:09

Hallo Zergling, Cycap,

Indexe sind gesetzt. String F könnte man durch integer ersetzen. Testweise zeigt es auch eine große Verbesserung der Perfomance - wobei ich mich frage, warum...Denn in table2 stehen bisher nur 10 Einträge - allerdings braucht die Abfrage immer noch über eine Sekunde. Also immer noch nicht vertretbar.

Werde mal testen, ob eine LEFT JOIN-Abfrage schneller ist und mich dann zurückmelden.

Gruß und Danke,

Peter

lazydog · 17.12.2007, 13:20

Code:

ORDER BY rand()

ist fast immer ein Performance-Killer. JOINS mit "!=" wahrscheinlich auch.
Prüfe mal die folgende Abfrage, die sollte eigentlich dasselbe Ergebnis bringen:

Code:

SELECT 
 	t1.s_id, 
 	t1.hip_nr, 
 	t1.details ... 
FROM 
 	table1 AS t1 
WHERE 
 	t1.hip_nr NOT IN(
 		SELECT 
 			t2.hip_nr
 		FROM
 			table2 AS t2
 		WHERE
 			t2.hip_nr = t1.hip_nr
 		  AND
 			prefix = 'A'
 	)

pepe24 · 17.12.2007, 14:30

Hallo allerseits,

PHP-Code:

  ORDER BY rand()

ist in diesem Fall - in der Tat - ein Perfomance-Killer von fast 3(!) Sekunden.
Die Lösung mit dem Subselect von lazydog funktioniert in diesem Fall sehr fix und zuverlässig, danke dafür.

Das randomizing habe ich nun einfach gestrichen, weil die Datensätze sowieso in mehr oder minder zufälliger Reihenfolge in der DB stehen.

Danke an alle Beteiligten und eine schöne Woche!

Gruß,
Peter

EDIT:
Hier noch die Fertige Abfrage. Nicht ganz lebensecht, weil sie durch ein Model läuft, aber ersichtlich, was hier vo sich geht:

PHP-Code:

  $select = $dbselect->from('sr_stars', array('id', 'hip_nr', 'details', 'hk', 'datetime'))

                ->where ("sr_stars.hip_nr NOT IN (SELECT hip_nr FROM sr_taufen WHERE sr_taufen.prefix = '".$value."')")

                ->limit(1)->query();

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
Problem mit Inner join	Jabi	Datenbanken	15	09.05.2006 14:10
INNER JOIN prob	lindner	Datenbanken	3	18.04.2006 17:37
INNER JOIN + Suchkriterien + Abfrage	duerov	PHP Tipps 2006	4	04.04.2006 12:47
inner join zeigt gleiche spalten nur 1x		Datenbanken	3	22.02.2006 17:11
inner join aus db		PHP Tipps 2006	8	22.01.2006 01:23
[Erledigt] Abfrageproblem mit Inner Join		Datenbanken	15	29.11.2005 18:17
sql-Abfrage inner join - unerklärliches Problem	havok	Datenbanken	6	17.10.2005 14:32
* und Inner Join?	Paulo	Datenbanken	2	27.09.2005 14:26
[Erledigt] INNER JOIN abfrage zusammen stricken		Datenbanken	3	04.07.2005 02:21
MySql - Inner Join?		PHP Tipps 2005-2	2	14.06.2005 15:24
INNER JOIN mit 4 tabellen ???	Dragon26mFR	Datenbanken	5	01.05.2005 18:51
[Erledigt] kleine inner join frage...		PHP-Fortgeschrittene	3	02.11.2004 22:02
inner join		Datenbanken	12	23.09.2004 13:22
INNER JOIN Fehler	Schaelle	Datenbanken	4	27.07.2004 19:12
INNER JOIN - realisieren?	Schaelle	PHP Tipps 2004	1	04.07.2004 09:07

17.12.2007, 12:36
pepe24 Erfahrener Benutzer Registriert seit: 21.07.2005 Beiträge: 209	exkludierender INNER JOIN Hallo, habe ein kleines JOIN-Problem. Es handelt sich hier um zwei Tabellen - wobei Tabelle 2 mit Daten aus Tabelle 1 befüllt wird. Das ganze sieht folgendermaßen aus: Table1: (ca. 10k Datensätze) s_id \| hip_nr \| details \| .... Table2: t_id \| hip_nr \| prefix \| .... Bisher sieht die Abfrage so (in etwa) aus: PHP-Code: `SELECT t1.s_id, t1.hip_nr, t1.details ... FROM table1 as t1 INNER JOIN table2 as t2 on(t1.hip_nr != t2.hip_nr) WHERE prefix = 'A' ORDER BY rand() LIMIT 1;` Erklärung "prefix": Beim Schreiben in t2 wird ein Prefix gesetzt, sinn dahinter ist, dass man t1 wieder von vorne in t2 eintragen kann, nur mit einem anderen Prefix. (mehrere Cycles); Sollte bei einer Erklärung aber unberücksichtigt bleiben, denn es macht die Sache unnötig kompliziert. Sinn an der ganzen Sache ist, dass mir die Abfrage heraussucht: - welche hip_nr aus t1 steht noch nicht in t2 Problem mit meiner Abfrage ist weniger, dass sie nicht die richtigen Ergebnisse liefert (das tut sie), sondern mehr, dass sie über 4 Sekunden dafür braucht, was einfach nicht tragbar ist. Danke für Eure Mühe. Gruß, Peter


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

17.12.2007, 12:49
cycap Moderator Registriert seit: 13.02.2008 Beiträge: 6.816 PHP-Kenntnisse: Fortgeschritten	Sowas? (Bin mir nicht sicher ob ich dich verstanden hab) Code: SELECT... FROM table1 AS t1 LEFT JOIN table 2 AS t2 ON t1.hip_nr = t2.hip_n WHERE t2.hip_nr IS NULL

17.12.2007, 12:53
Zergling-new Erfahrener Benutzer Registriert seit: 21.05.2008 Beiträge: 9.937	Re: exkludierender INNER JOIN Hallo, wenn die Abfrage solange dauert, fehlen vielleicht die Indexe: Die sollten für alle Verknüpfungsfelder und alle Bedingungen gesetzt werden. Vielleicht ist "Prefix" als Stringfeld auch nicht so geeignet. Läßt sich das ganze auf numerische Werte umstellen? Zieh mal das prefix = 'A' in die Join-Bedingung (ON condition1 AND condition2), vielleicht ist das schneller, weil die Restriktion früher in Kraft tritt und somit weniger Zwischenergebnisse anfallen (wobei das auch nur eine Vermutung ist).

17.12.2007, 13:09
pepe24 Erfahrener Benutzer Registriert seit: 21.07.2005 Beiträge: 209	Hallo Zergling, Cycap, Indexe sind gesetzt. String F könnte man durch integer ersetzen. Testweise zeigt es auch eine große Verbesserung der Perfomance - wobei ich mich frage, warum...Denn in table2 stehen bisher nur 10 Einträge - allerdings braucht die Abfrage immer noch über eine Sekunde. Also immer noch nicht vertretbar. Werde mal testen, ob eine LEFT JOIN-Abfrage schneller ist und mich dann zurückmelden. Gruß und Danke, Peter

17.12.2007, 13:20
lazydog Erfahrener Benutzer Registriert seit: 02.04.2008 Beiträge: 4.032	Code: ORDER BY rand() ist fast immer ein Performance-Killer. JOINS mit "!=" wahrscheinlich auch. Prüfe mal die folgende Abfrage, die sollte eigentlich dasselbe Ergebnis bringen: Code: SELECT t1.s_id, t1.hip_nr, t1.details ... FROM table1 AS t1 WHERE t1.hip_nr NOT IN( SELECT t2.hip_nr FROM table2 AS t2 WHERE t2.hip_nr = t1.hip_nr AND prefix = 'A' ) __________________ Gruss L

17.12.2007, 14:30
pepe24 Erfahrener Benutzer Registriert seit: 21.07.2005 Beiträge: 209	Hallo allerseits, PHP-Code: `ORDER BY rand()` ist in diesem Fall - in der Tat - ein Perfomance-Killer von fast 3(!) Sekunden. Die Lösung mit dem Subselect von lazydog funktioniert in diesem Fall sehr fix und zuverlässig, danke dafür. Das randomizing habe ich nun einfach gestrichen, weil die Datensätze sowieso in mehr oder minder zufälliger Reihenfolge in der DB stehen. Danke an alle Beteiligten und eine schöne Woche! Gruß, Peter EDIT: Hier noch die Fertige Abfrage. Nicht ganz lebensecht, weil sie durch ein Model läuft, aber ersichtlich, was hier vo sich geht: PHP-Code: `$select = $dbselect->from('sr_stars', array('id', 'hip_nr', 'details', 'hk', 'datetime')) ->where ("sr_stars.hip_nr NOT IN (SELECT hip_nr FROM sr_taufen WHERE sr_taufen.prefix = '".$value."')") ->limit(1)->query();`