SQL INNER JOIN

[Johann]

Hallo Leute,

ich hab eine SQL-Frage. Bin da ein ziemlicher Neuling und tu mich mit den JOINs etwas schwer.

Meine Mysql-db hat in Tabelle A 700.000 records und in Tabelle B 600.000 records.

Tabelle A
number,serial,name,description,date

Tabelle B
number,serial,value,date

Nun möchte ich 2 Querys erstellen.

1. Ich möchte alle Einträge von Tabelle B haben, bei denen number und serial auch in Tabelle A existieren.
   Mein Versuch:

SELECT *
FROM TabelleB
INNER JOIN TabelleA
ON TablleA.serial=TabelleB.serial AND TabelleA.number=TabelleB.number
WHERE TabelleB.serial LIKE '%812%'
AND DATE( TabelleB.date ) = '2009-11-19'

Der Query läuft auch und läuft und läuft und läuft. Der MYSQL Deamon schnappt sich die komplette CPU und die Abfrage spuckt auch
nach 5 Min noch nichts aus. Stimmt die Abfrage so? Ist es "nur" ein Performanceproblem?

Denn die Abfrage...
SELECT *
FROM TabelleB
WHERE serial LIKE '%812%'
AND DATE( date ) = '2009-11-19
... spuckt mir in unter 1 Sekunde die knapp 5000 records aus.

2. Ich möchte alle Einträge von Tabelle B haben, bei denen number und serial  in Tabelle A NICHT existieren.

Noch kein Versuch.

Danke für eure Hilfe.

ciao,
Johann

[dedlfix]

Hi!

1. Ich möchte alle Einträge von Tabelle B haben, bei denen number und serial auch in Tabelle A existieren.
   SELECT *
   FROM TabelleB
   INNER JOIN TabelleA
   ON TablleA.serial=TabelleB.serial AND TabelleA.number=TabelleB.number
   WHERE TabelleB.serial LIKE '%812%'
   AND DATE( TabelleB.date ) = '2009-11-19'

Der Query läuft auch und läuft und läuft und läuft. [...] Ist es "nur" ein Performanceproblem?

Vermutlich. Was sagt EXPLAIN? Vermutlich, dass keine Indexe - so vorhanden - für die WHERE-Klausel verwendet werden können.

TabelleB.serial LIKE '%812%'

ist ungünstig, weil es keinen "Mitten"-Index gibt. Es muss also jeder Datensatz einzeln durchgesehen werden.

DATE( TabelleB.date ) = '2009-11-19'

Die Formel um die Tabellenspalte sorgt im Allgemeinen auch dafür, dass sie einzeln für jeden Datensatz berechnet werden muss. Wenn in der Spalte, die "date" heißt, stattdessen ein DATETIME-Wert enthalten ist, dann sorgt ein

TabelleB.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'

schon einmal dafür, dass überhaupt ein Index verwendet werden kann.

2. Ich möchte alle Einträge von Tabelle B haben, bei denen number und serial  in Tabelle A NICHT existieren.

Das bekommt man üblicherweise über einen OUTER JOIN (LEFT JOIN, RIGHT JOIN). Bei einem solchen werden alle Datensätze der einen Tabelle verwendet, auch wenn es keine passende Gegenstücke gibt. In diesem Fall ist NULL statt der Werte der Spalten der zweiten Tabelle im Ergebnis. Darauf kann man testen.

Lo!

[Johann]

Der Query läuft auch und läuft und läuft und läuft. [...] Ist es "nur" ein Performanceproblem?

Vermutlich. Was sagt EXPLAIN? Vermutlich, dass keine Indexe - so vorhanden - für die WHERE-Klausel verwendet werden können.

EXPLAIN sagt:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra
1 SIMPLE TabelleB ALL NULL NULL NULL NULL 549968 Using where
1 SIMPLE TabelleA ALL NULL NULL NULL NULL 637871 Using where

Muss ich diese Indexe selber erzeugen? Wenn ja, wie?

Das bekommt man üblicherweise über einen OUTER JOIN (LEFT JOIN, RIGHT JOIN). Bei einem solchen werden alle Datensätze der einen Tabelle verwendet, auch wenn es keine passende Gegenstücke gibt. In diesem Fall ist NULL statt der Werte der Spalten der zweiten Tabelle im Ergebnis. Darauf kann man testen.

Hättest du mir die Syntax für meine Tabelle, so dass ich das mal laufen lassen kann, ob da die Abfrage schneller durchläuft?

Danke.

[dedlfix]

Hi!

Muss ich diese Indexe selber erzeugen? Wenn ja, wie?

Ja, natürlich. Index-Anlegen ist Grundlagenwissen. Mit dem phpMyAdmin beispielsweise ist das Anlegen über eine Spalte nicht schwer. Bedenke aber, dass "viel" nicht automatisch viel hilft, sondern nur Verwaltungsoverhead beim Datenändern kostet. Leg also nur die Indexe an, die benötigt werden und überzeuge dich von ihrer Verwendung mit EXPLAIN.

Das bekommt man üblicherweise über einen OUTER JOIN (LEFT JOIN, RIGHT JOIN). [...]
Hättest du mir die Syntax für meine Tabelle, so dass ich das mal laufen lassen kann, ob da die Abfrage schneller durchläuft?

Das hat nichts mit schneller oder langsamer zu tun. Das war die Antwort auf deine fachliche Anforderung, alles A zu bekommen, zu dem es kein B gibt. Für Performance sorgt dann wieder ein passender Index. Wenn du dich mit JOINs und deren Syntax nicht auskennst, so lies bitte die beiden Artikel dazu:

Einführung in Joins
Fortgeschrittene Jointechniken

Lo!

[Johann]

Ja, natürlich. Index-Anlegen ist Grundlagenwissen. Mit dem phpMyAdmin beispielsweise ist das Anlegen über eine Spalte nicht schwer.

Okay, mit einem Index auf die Spalten läuft es jetzt einwandfrei.

Das bekommt man üblicherweise über einen OUTER JOIN (LEFT JOIN, RIGHT JOIN). [...]

Ich würde mich freuen, wenn du die Lösung für mein Problem zeigen könntest - denn ich komme auch nicht mit der Lektüre drauf.

[dedlfix]

Hi!

Ich würde mich freuen, wenn du die Lösung für mein Problem zeigen könntest - denn ich komme auch nicht mit der Lektüre drauf.

Zeig was du hast, dann kann dir auch jemand den Fehler zeigen. Üblicherweise hat man ein

... linke_tabelle LEFT JOIN rechte_tabelle ON ... WHERE rechte_tabelle.feld IS NULL

Lo!

[Johann]

Hi!

Ich würde mich freuen, wenn du die Lösung für mein Problem zeigen könntest - denn ich komme auch nicht mit der Lektüre drauf.

Zeig was du hast, dann kann dir auch jemand den Fehler zeigen. Üblicherweise hat man ein

... linke_tabelle LEFT JOIN rechte_tabelle ON ... WHERE rechte_tabelle.feld IS NULL

Okay, nochmal kurz zusammengefasst

Ich will alle Spalten von Tabelle B bei der die serial zu Tabelle A identisch ist, aber die number nur in Tabelle B exisitert.

Tabelle A
number,serial,name,description,date

Tabelle B
number,serial,value,date

Mein Versuch mit 0 resulst:

SELECT *  
FROM B  
LEFT JOIN A  
ON B.serial=A.serial  
WHERE B.serial LIKE '%999%'  
AND B.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'  
AND A.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'  
AND A.number is NULL  
GROUP BY B.number;

[dedlfix]

Hi!

Ich will alle Spalten von Tabelle B bei der die serial zu Tabelle A identisch ist, aber die number nur in Tabelle B exisitert.
Mein Versuch mit 0 resulst:

SELECT *

FROM B
LEFT JOIN A
ON B.serial=A.serial
WHERE B.serial LIKE '%999%'
AND B.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'
AND A.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'
AND A.number is NULL
GROUP BY B.number;

  
Was macht das GROUP BY da?  
  
  
Lo!

[johann]

Hi!

Ich will alle Spalten von Tabelle B bei der die serial zu Tabelle A identisch ist, aber die number nur in Tabelle B exisitert.
Mein Versuch mit 0 resulst:

SELECT *

FROM B
LEFT JOIN A
ON B.serial=A.serial
WHERE B.serial LIKE '%999%'
AND B.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'
AND A.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'
AND A.number is NULL
GROUP BY B.number;

>   
> Was macht das GROUP BY da?  
>   
  
  
Gruppieren?!  
Es gibt mehrere Einträge mit gleicher number und serial, aber anderem name. Die interessieren mich aber nicht. Ist das von Bedeutung? Zumindest ändert es nichts, wenn ich es raunehme.  

[dedlfix]

Hi!

AND A.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'
AND A.number is NULL

Das müsste der Fehler sein: Wenn kein A-Datensatz vorhanden ist, kann dessen "date" auch nicht im angegebenen Bereich liegen.

Lo!

[Johann]

Hi!

AND A.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'
AND A.number is NULL

Das müsste der Fehler sein: Wenn kein A-Datensatz vorhanden ist, kann dessen "date" auch nicht im angegebenen Bereich liegen.

Nö, geht trotzdem nicht. 0 Zeilen. Ausserdem ist die Frage ob A.number is NULL richtig ist, A.number nicht NULL ist, sondern der Datensatz schlicht nicht existiert.

[dedlfix]

Hi!

AND A.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'
AND A.number is NULL
Das müsste der Fehler sein: Wenn kein A-Datensatz vorhanden ist, kann dessen "date" auch nicht im angegebenen Bereich liegen.
Nö, geht trotzdem nicht. 0 Zeilen.

Was konkret hast du gemacht? Die erste der zitierten Zeilen entfernt, oder was anderes?

Ausserdem ist die Frage ob A.number is NULL richtig ist, A.number nicht NULL ist, sondern der Datensatz schlicht nicht existiert.

Ja, das ist so bei einem Outer Join. Du verbindest zwei Tabellen und hast im Ergebnis eine Anzahl Spalten aus beiden Tabellen. Nur weil in der einen Tabelle kein Datensatz existiert ändert sich nicht die Spaltenanzahl der Ergebnismenge für diesen einen Eintrag. Stattdessen werden die nicht vorhandenen Werte durch NULL ersetzt. Und genau deshalb kann man mit IS NULL nach nicht vorhandenen Datensätzen fragen.

Lo!

[Johann]

Hi!

AND A.date BETWEEN '2009-11-19 00:00:00' AND '2009-11-19 23:59:59'
AND A.number is NULL
Das müsste der Fehler sein: Wenn kein A-Datensatz vorhanden ist, kann dessen "date" auch nicht im angegebenen Bereich liegen.
Nö, geht trotzdem nicht. 0 Zeilen.

Was konkret hast du gemacht? Die erste der zitierten Zeilen entfernt, oder was anderes?

Ja, die erste Zeile entfernt.

[Ilja]

yo,

1. Ich möchte alle Einträge von Tabelle B haben, bei denen number und serial auch in Tabelle A existieren.

ich habe in den beiträgen mit dedlfix nicht alles gelesen, aber du hast einen falschen ansatz. joins sind in diesem fall der falsche weg. du willst ja nicht beide tabllen miteinander verknüpfen, sondern du willst nur datensätze der tabelle b in der ergebnismenge haben. und dazu sind joins in aller regel ungeeignet. sie können zwar zum gleichen ergebnis führen, das ist aber abhängig von den werten in den beiden tabellen. besser wäre hier der einsatz von sogenannten korrelierten unterabfragen mit der pürfung auf EXIST.

SELECT b.number, b.serial, b.value, b.date
FROM TabelleB b
WHERE DATE(b.date ) = '2009-11-19'
AND EXISTS (SELECT NULL
            FROM TabelleA a
            WHERE a.serial = b.serial
            AND a.number = b.number
           )
;

2. Ich möchte alle Einträge von Tabelle B haben, bei denen number und serial  in Tabelle A NICHT existieren.

die zweite abfrage ist dann schon fast selbsterklärend, wenn man die erste hat.

SELECT b.number, b.serial, b.value, b.date
FROM TabelleB b
WHERE DATE(b.date ) = '2009-11-19'
AND NOT EXISTS (SELECT NULL
                FROM TabelleA a
                WHERE a.serial = b.serial
                AND a.number = b.number
               )
;

Ilja