Subselect als Join

[Klaus]

Hallo, ich habe die folgende Abfrage

SELECT count(id) as anzahl  
FROM results r1  
WHERE scan_id <= ? AND ip = ? AND p_id NOT IN  
 ( SELECT r2.p_id FROM results r2 WHERE scan_id <= ? AND ip != ? AND scan_id > r1.scan_id)  
ORDER BY id LIMIT 1;

Jetzt habe ich sehr große Performance Probleme. Auch unter Verwendung von Indices.

Ich habe schon gegoogelt und folgendes gefunden:

http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect

Kann mir jemand erklären, wie ich das auf meine Abfrage anwende? Ich komme da einfach nicht weiter :(

Danke

Klaus

[Vinzenz Mai]

Hallo Klaus,

SELECT count(id) as anzahl

FROM results r1
WHERE scan_id <= ? AND ip = ? AND p_id NOT IN
( SELECT r2.p_id FROM results r2 WHERE scan_id <= ? AND ip != ? AND scan_id > r1.scan_id)
ORDER BY id LIMIT 1;

  

> Jetzt habe ich sehr große Performance Probleme. Auch unter Verwendung von Indices.  
  

> <http://www.codersrevolution.com/index.cfm/2008/7/31/MySQL-performance-INNER-JOIN-vs-subselect>  
  

> Kann mir jemand erklären, wie ich das auf meine Abfrage anwende?  
  
ich habe das schon erklärt: ein [Selfjoin](http://aktuell.de.selfhtml.org/artikel/datenbanken/fortgeschrittene-joins/selfjoin.htm) in Verbindung mit einem [Thetajoin](http://aktuell.de.selfhtml.org/artikel/datenbanken/fortgeschrittene-joins/thetajoin.htm):  
  
Soweit ich Dich verstehe möchtest Du die Anzahl des Auftreten der kleinsten id der Datensätze haben, die folgende Bedingungen erfüllen:  
  
a) die scan\_id ist kleiner oder gleich ein bestimmter Wert  
b) die ip-Adresse hat einen bestimmten Wert  
c) die p\_id ist nicht in der Menge der p\_ids enthalten, die aus  
   Datensätzen der gleichen Tabelle stammen, die wiederum bestimmte  
   Bedingungen erfüllen:  
  
   1) die scan\_id liegt zwischen zwei bestimmten Werten  
   2) die ip ist ungleich einem bestimmten Wert  
   (beachte, dass 1) zwei Bedingungen zusammenfasst :-))  
  
Somit können wir grundsätzlich schon mal das ganze mit einem Selfjoin umschreiben:  
  
~~~sql
SELECT  
    r1.id  
FROM  
    results r1  
INNER JOIN  
    results r2  
ON  
    p1.p_id = r2.p_id  
WHERE  
    r1.scan_id <= ? AND ip = ? AND  
    -- mit ein paar redundanten Klammern  
    -- die Bedingung Deines Subselects  
    NOT ( (r2.scan_id BETWEEN ? AND ?) AND (r2.id != ?) )  

Darauf COUNT und LIMIT anzuwenden, solltest Du selbst hinbekommen.

Nicht (a UND b) ist übrigens äquivalent zu (nicht a) ODER (nicht b),
was Du nutzen könntest, um die Bedingung positiver und lesbarer zu gestalten.

r2.scan_id NOT BETWEEN ? AND ? OR r2.id = ?

Achtung: Eine kleine Ungenauigkeit habe ich mir geleistet:

expr BETWEEN wert1 AND wert2 entspricht expr >= wert1 AND expr <= wert2, d.h. beide Grenzen werden mitgenommen. Du müsstest, da Du einmal die Grenze nicht mitnimmst, den übergebenen Grenzwert anpassen (falls dies möglich ist). Wenn nicht, dann formuliere die Bedingungen wie bisher.

Befrage EXPLAIN, was es von den verschiedenen Varianten hält.

Freundliche Grüße

Vinzenz

[Klaus]

Danke für deine Antwort Vinzenz! Ich habe das gleich mal ausprobiert. Die Anzahl an Ergebnissen zwischen den beiden Möglichkeiten ist jedoch unterschiedlich:

Meine Variante

SELECT count(id) as anzahl  
FROM results r1  
WHERE scan_id <= 4 AND ip = '3568098565' AND p_id NOT IN  
 ( SELECT r2.p_id FROM results r2 WHERE scan_id <= 4 AND ip != '3568098565' AND scan_id > r1.scan_id)  
LIMIT 1;

--> 1524 Ergebnisse

Deine Variante

SELECT  
    r1.id  
FROM  
    results r1  
INNER JOIN  
    results r2  
ON  
    r1.p_id = r2.p_id  
WHERE  
    r1.scan_id <= 4 AND r1.ip = '3568098565' AND  
    NOT ( r2.scan_id <= 4 AND  r2.ip != '3568098565' AND r2.scan_id > r1.scan_id );

--> 1525 Ergebnisse

Hast du noch eine Idee?

Danke und Grüße

Klaus

[Vinzenz Mai]

Hallo,

Danke für deine Antwort Vinzenz! Ich habe das gleich mal ausprobiert. Die Anzahl an Ergebnissen zwischen den beiden Möglichkeiten ist jedoch unterschiedlich:

--> 1524 Ergebnisse
--> 1525 Ergebnisse

Hast du noch eine Idee?

ermittle den unterschiedlichen Datensatz, prüfe, ob er gewüscht ist und warum er durch Statement a) gefunden wurde und durch Statement b) nicht.

Sind es eventuell sogar mehr Datensätze, die sich in beiden Ergebnismengen unterscheiden? Ohne Daten kann ich dazu nichts sagen. Ich hab die Logik aus Deinem Statement herausgelesen.

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo,

SELECT count(id) as anzahl

FROM results r1
WHERE scan_id <= 4 AND ip = '3568098565' AND p_id NOT IN
( SELECT r2.p_id FROM results r2 WHERE scan_id <= 4 AND ip != '3568098565' AND scan_id > r1.scan_id)
LIMIT 1;

>   
> --> 1524 Ergebnisse  
> --> 1525 Ergebnisse  
  

> Hast du noch eine Idee?  
  
ja. Kurze Pausen nicht für einen Forumsbeitrag nutzen.  
Meine Lösung setzt voraus, dass p\_id in der Tabelle results eindeutig ist.  
  
  
Freundliche Grüße  
  
Vinzenz