Performance-Problem bei LEFT JOIN mit IS NULL

[thommyk]

Hallo

Ich hab 2 Tabellen in denen jeweils zu einer objektnummer verschiedene
Werte drin stehen.
Jetzt will ich kontrollieren, welche Nummern in der einen Tabelle fehlen, die in der anderen Tabelle vorhanden sind.

Also

SELECT tabelle2.objnr FROM
tabelle1
LEFT JOIN tabelle2 USING (objnr)
WHERE tabelle2.objnr IS NULL 

(mal vereinfacht, sind noch ein paar andere WHERE bedingungen, die nicht wichtig sind).
Leider ist die Anzahl der ausgewählten Zeilen von Tabelle1 manchmal bei ca 50000 und vor allem, wenn keine Unterschiede bestehen, also als Ergebnis keine Zeile rauskommt, braucht die Abfrage z.T über 1 Minute
Weil sie dann auch Tabelle1 blockiert, legt dass den halben Server lahm.

Gibt´s ne andere Möglichkeit, die Abfrage hinzukriegen, oder
sollte ich mal ne php Lösung probieren?
Also

$Objects1 = Objektnummern Tabelle1
$Objects2 = Objektnummern Tabelle2
$Objects = array_diff($Objects1, $Objects2);

$query = "SELECT * from Tabelle 2 WHERE objnr IN ('".implode("','", $Objects)."')"; 

Könnte aber evtl am Speicher scheitern
Ausserdem sind dass dann 3 Abfragen, was auch mit einer gehen sollte.
Wer weiss mehr?

[asp2php]

und wie ist mit:


SELECT a.objnr FROM tabelle1 a LEFT JOIN tabelle2 b ON a.objnr=b.objnr AND b.objnr IS NULL

[thommyk]

SELECT a.objnr FROM tabelle1 a LEFT JOIN tabelle2 b ON a.objnr=b.objnr AND b.objnr IS NULL

Geht nicht, weil dann der JOIN nicht richtig funzt.
Als Ergebnis kommen dann alle Datensätze von Tabelle1 raus,weil alle
Felder von Tabelle2 NULL sind.
Aber netter Versuch.

[asp2php]

du sagtest, du willst kontrollieren, ob in tabelle2 Einträge fehlen, die in tabelle1 existieren, stimmt? mit meiner Abfrage bekommst du genau das. Oder was willst du eigentlich ...

[thommyk]

mit meiner Abfrage bekommst du genau das. Oder was willst du eigentlich

Beispiel:
In Tabelle1 sind 60.000 Nummern drin, in Tabelle2 nur 59.995.
Ich will die 5 Nummern, die in Tabelle1 drin sind und in Tabelle2 fehlen.

Deine Abfrage liefert mir aber ALLE Datensätze der Tabelle1.
Oder ich bin zu blöd, sie richtig zu formulieren, was ich aber ausschliessen würde, weils ja nicht so schwer ist.

[w.churchll]

Hast du auf die objnr einen Index drauf?

wenn nicht mach mal einen drauf.
dürfte das ganze deutlich beschleunigen.

[chansel0049]

Ist es nicht einfacher mit Subselect zu arbeiten?

Etwa so :

SELECT objnr FROM tabelle1 WHERE
objnr NOT IN  (
SELECT t1.objnr FROM
tabelle1 t1 INNER JOIN tabelle2 t2 USING (objnr)
) 

[w.churchll]

korrigiert mich wenn ich mich irre, aber er arbeitet mit mysql und da gibt es subselects erst ab version 4.1 und die version ist erst seit ein paar monaten stabiel.

[chansel0049]

richtig, richtig, und eh, noch mal richtig

aber warum nicht upgraden, jetzt wos stabil läuft?

[Sebastian W]

Hm, hab das ganze mal auf meiner Windows Kiste (um einiges langsamer als Online unter Linux) getestet und dabei nach nur: 0.5884 sek das Ergebnis gehabt.

EXPLAIN SELECT * FROM tabelle_a a LEFT JOIN tabelle_b b USING(id) WHERE b.id IS NULL

table  	type  	possible_keys	key  key_len  	ref  rows  	Extra  
a 	ALL 	NULL 	NULL 	NULL NULL 	50007   
b 	eq_ref 	PRIMARY	PRIMARY 4	a.id 	1 	Using where; Not exists

Hast du auch wirklich die Indexe gesetzt?

tabelle_a Datensätze = 50007
tabelle_b Datensätze = 50004

[chansel0049]

Bin jetzt nicht sicher aber in deiner Query

SELECT tabelle2.objnr FROM
tabelle1
LEFT JOIN tabelle2 USING (objnr)
WHERE tabelle2.objnr IS NULL 

von oben

Joinst du T1 mit t2 über left join Gibst als Feld für Select jedoch ein Feld in Tabelle 2 die in der Where clause jedoch auf NULL Werte begrenzt wird

Versuch mal

SELECT tabelle1.objnr FROM
tabelle1
LEFT JOIN tabelle2 USING (objnr)
WHERE tabelle2.objnr IS NULL 

oder vielleicht sogar etwas anders :

SELECT t1.objnr FROM tabelle1 LEFT JOIN tabelle2 USING (objnr)
GROUP BY t1.objnr
HAVING count(DISTINCT t2.objnr) <1 

Allerdings jetzt nur so n Gedanke ...

[thommyk]

Ihr diskutiert ja munter ohne mich, aber gut.

Erst mal zum Thema index: Klar sind indexe auf den wichtigen Feldern wie der objnr.
Bei dem selektierten Feld hab ich mich vertan, muss natürlich
Select tabelle1.objnr FROM ...
heissen

Ich habe die Abfrage auch etwas vereinfacht dargestellt, es gibt noch einige andere Bedingungen und die Tabellen enthalten beide
über 100.000 Einträge wovon ich jeweils nur einen Teil vergleiche.
Durch die zusätzlichen Bedingungen dauert das ganze dann so lange.

SELECT objnr FROM tabelle1 WHERE
objnr NOT IN (
SELECT t1.objnr FROM
tabelle1 t1 INNER JOIN tabelle2 t2 USING (objnr)
)

Die Idee mit der Subquery ist IMHO schlecht, weil kein Index benutzt werden kann wenn mit NOT IN gearbeitet wird, genauso wie bei
objnr != '$objnr'

Ich hab den Vergleich auch mal über zwei Arrays probiert, aber bei array_diff verabschiedet sich php mangels Speicher (16 MB).
Möchte php aber auch per default nicht mehr als 16MB geben (ist schon reichlich).
Zur Zeit werden die Abfragen per cron nur nachts ausgeführt, wenn die Belastung nicht so hoch ist.
Werd wohl nicht drum rum kommen, noch mehr RAM einzubauen

Aber danke für alles mitdenken