Subquery eigentlich unkorreliert, MySQL meint aber korreliert

[Chris]

Hallo,

ich habe zwei Tabellen, eine mit Produkten 'products', eine mit Listen 'lists'. Diese stehen zueinander in einer m:n-Beziehung. Ein Produkt kann also in mehreren Listen enthalten sein und eine Liste kann mehrere Produkte zusammenfassen. Dafür habe ich eine Verknüpfungstabelle 'prods_lists'.

Mein Ziel:
Ich möchte sämtliche Produkte haben mit Ausnahme von solchen, die in ganz bestimmten Listen enthalten sind. Die IDs dieser speziellen Listen habe ich, sagen wir mal 1, 2 und 3.

Es läuft MySQL v5.0.30
Das ganze löse ich mit einem doppelten Subquery:

  
SELECT id_products  
FROM products  
WHERE id_products NOT IN  
    (SELECT id_products  
     FROM prods_lists AS pl1  
     WHERE EXISTS  
         (SELECT id_lists  
          FROM prods_lists AS pl2  
          WHERE pl2.id_products = pl1.id_products  
            AND pl2.id_lists IN (1,2,3)  
          LIMIT 1  
         )  
    )  

Produkte gibt es insgesamt etwa 30000. Produkte, die durch die speziellen Listen ausgeschlossen werden einige Hundert. Trotzdem dauert die Anfrage gut 30 Sekunden.
Meine erste Frage: Gibt es in meiner Anfrage grobe Nachlässigkeiten bzgl. Optimierung? Gibt es eine bessere Query? Ein Join wäre vielleicht schneller, aber ich bekomme ihn nicht hin, weil ein Produkt ja in mehreren Listen sein kann.

Nun habe ich mir das mal EXPLAINen lassen und es stellt sich heraus, dass die beiden Subselect beide abhängig sind, aber das sind sie doch gar nicht (nur die zweite natürlich)?
Die Query habe ich dann erstmal vereinfacht:

  
SELECT id_products  
FROM products  
WHERE id_products NOT IN  
    (SELECT DISTINCT id_products  
     FROM prods_lists  
    )  

Hier ist ja offensichtlich, dass die Subquery unabhängig ist, aber MySQL schreibt bei Explain "Dependent subquery" hin und die ganze Query braucht immer noch 15 Sekunden.
Wenn ich das Ergebnis dieser Subquery manuell in die "NOT-IN-Liste" eintrage, ist das Ergebnis sofort da.
Für "possible_keys" nennt MySQL beim äußeren select "NULL", bei "keys " aber "PRIMARY".

Ich würde mich freuen, wenn ihr mich über das für mich seltsame Verhalten aufklären könntet! Danke!

Chris

[_King Lully]

  
SELECT  
 id_products  
FROM  
 products  
WHERE  
 id_products NOT IN  
  (  
  SELECT DISTINCT  
   id_products  
  FROM  
   prods_lists  
  )  

Hier ist ja offensichtlich, dass die Subquery unabhängig ist, aber MySQL schreibt bei Explain "Dependent subquery" hin und die ganze Query braucht immer noch 15 Sekunden.

Wie ist denn eine dependent und eine independent subquery definiert?

Wenn ich das Ergebnis dieser Subquery manuell in die "NOT-IN-Liste" eintrage, ist das Ergebnis sofort da.

Eventuell einen Index auf 'id_products' in 'prods_lists' setzen.

Für "possible_keys" nennt MySQL beim äußeren select "NULL", bei "keys " aber "PRIMARY".

Was heisst das denn?

(Ach ja, bitte sinnvolle variablennamen wählen!)

[Chris]

Hallo _King Lully

Wie ist denn eine dependent und eine independent subquery definiert?

Definition aus der Doku von MySQL:

Eine "korrelierte Unterabfrage" ist eine Unterabfrage, die eine Tabelle referenziert, die auch in der äußeren Abfrage erscheint.

Ich habe hier die Def. einer "korrelierten", weil bei EXPLAIN zu finden ist:

DEPENDENT bezeichnet normalerweise die Verwendung einer korrelierten Unterabfrage.

In der äußeren Abfrage wird die Tabelle "products" referenziert, in der inneren die Verknüpfungstabelle "prods_lists" und nirgends die Tabelle "products" der äußeren Abfrage. Also ist die Subquery INdependent. So sehe ich das.

Eventuell einen Index auf 'id_products' in 'prods_lists' setzen.

id_products ist PRIMARY KEY in der Tabelle "products". In der Verknüpfungstabelle gibt es einen gemeinsamen Index auf die Felder "id_products" und "id_lists", so wie es sich für eine Verknüpfungstabelle gehört.

Für "possible_keys" nennt MySQL beim äußeren select "NULL", bei "keys " aber "PRIMARY".

Was heisst das denn?

Das frage ich mich auch. Warum findet MySQL keinen möglichen Key, verwendet dann aber doch einen? In der Doku hatte ich auch lange gesucht, aber außer einer trivialen Beschreibung von "possible_keys" (das sind die Keys, die für MySQL in Frage kommen) habe ich nichts gefunden.

(Ach ja, bitte sinnvolle variablennamen wählen!)

Ich weiß leider nicht, was du damit meinst. Primary Keys nenne ich immer so "id_<TabellenName>" und in der Verknüpfungstabelle ist "id_products" ein Fremdschlüssel.

Schöne Grüße
Chris

[_King Lully]

(Ach ja, bitte sinnvolle variablennamen wählen!)

Ich weiß leider nicht, was du damit meinst. Primary Keys nenne ich immer so "id_<TabellenName>" und in der Verknüpfungstabelle ist "id_products" ein Fremdschlüssel.

Du solltest Konventionen haben für die Datenfeldnamen und die Tabellennamen. Tabellennamen kann man - überraschenderweise - gut nach den Entitäten benennen, auf die sie verweisen, die Plural-Nutzung empfiehlt sich, also bspw. 'Rights', 'Sessions', 'Contracts' etc. (englische Namensgebung nicht unbed. erforderlich ;).

Datenfeldnamen benennen wir so:
<Tabellenname>_<Datenfeldname>

Also bspw. 'Sessions_ID'. Warum nicht 'Session_ID'? Nun, gute Frage, manche Pluralformen sind mit den Singularformen identisch und dann gibts einen Regelbruch.

Vorteile der konsistente Namensgebung:

• konsistente Namensgebung   ;)
• DB-eindeutige Namen (sehr gut für komplizierte Queries)
• Denkleistung des Programmierers darf sich wirklich wichtigen Sachverhalten widmen

[Chris]

Hallo,

danke für deine Erläuterungen bzgl. der Benamsung der Felder!
Darüber mach ich mir auch einige Gedanken und momentan bin ich noch zufrieden mit meinen Namen :)
Den Tabellennamen als Präfix zu benutzen überlege ich mir nochmal, dafür hast du ja einige gute Argumente genannt.

Könntest du bitte trotzdem nochmal auf das eigentliche Problem eingehen? Ich bin immer noch der Meinung, dass obige Unterabfrage unkorreliert ist, bzw. ich verstehe nicht, warum MySQL diese als "dependent" markiert.

Danke und Grüße
Chris

[Vinzenz Mai]

Hallo Chris,

Eventuell einen Index auf 'id_products' in 'prods_lists' setzen.

id_products ist PRIMARY KEY in der Tabelle "products". In der Verknüpfungstabelle gibt es einen gemeinsamen Index auf die Felder "id_products" und "id_lists", so wie es sich für eine Verknüpfungstabelle gehört.

Nein, dieser ist nicht ausreichend. Ist id_lists das erste Feld im gemeinsamen Index, so erstelle einen eigenen für id_products. Für id_lists erübrigst sich das, da dies im gemeinsamen Index bereits enthalten ist.

Weiterhin ist das innere Subselect überflüssig. Den Zugriff auf die Listen erhältst Du direkt aus der Verknüpfungstabelle.

Freundliche Grüße

Vinzenz

[Chris]

Hallo Vinzenz,

Nein, dieser ist nicht ausreichend. Ist id_lists das erste Feld im gemeinsamen Index, so erstelle einen eigenen für id_products. Für id_lists erübrigst sich das, da dies im gemeinsamen Index bereits enthalten ist.

id_products ist das erste Feld im gemeinsamen Index.

Weiterhin ist das innere Subselect überflüssig. Den Zugriff auf die Listen erhältst Du direkt aus der Verknüpfungstabelle.

Kannst du mir sagen, wie ich die Abfrage ansonsten gestalten soll? Mit Joins habe ich noch keine Lösung gefunden. Bei jedem Versuch ist die Komplexität weit über meine Lösung mit dem Subselect gestiegen.

Schöne Grüße
Christian

[Vinzenz Mai]

Hallo Christian,

Nein, dieser ist nicht ausreichend. Ist id_lists das erste Feld im gemeinsamen Index, so erstelle einen eigenen für id_products. Für id_lists erübrigst sich das, da dies im gemeinsamen Index bereits enthalten ist.

id_products ist das erste Feld im gemeinsamen Index.

dann erstelle einen Index für id_lists :-)
Das ist wichtig, das ist richtig, das ist sinnvoll, selbst wenn es in dieser speziellen Abfrage vielleicht noch nicht einmal gebraucht wird.

Weiterhin ist das innere Subselect überflüssig. Den Zugriff auf die Listen erhältst Du direkt aus der Verknüpfungstabelle.

Kannst du mir sagen, wie ich die Abfrage ansonsten gestalten soll? Mit Joins habe ich noch keine Lösung gefunden. Bei jedem Versuch ist die Komplexität weit über meine Lösung mit dem Subselect gestiegen.

Nein, Du benötigst keinen Join - auch wenn ich Joins mag:

SELECT  
    p.id_products                  -- ich gehe davon aus, dass Du an weiteren  
                                   -- Spalten interessiert bist und nicht jeder  
                                   -- Eintrag in der Tabelle products einen Eintrag  
                                   -- in prod_lists haben muss :-)  
FROM products p  
WHERE p.id_products NOT IN (       -- wobei die id_products nicht in der Liste  
    SELECT                         -- der id_products auftaucht  
        pl.id_products  
    FROM prods_lists pl            -- die in der Verknüpfungstabelle  
    WHERE pl.id_lists IN (1, 2, 3) -- einer von ein paar ausgewählten Listen zugeordnet sind  
)

Du hast Dein Subselect viel zu kompliziert aufgebaut :-)

EXPLAIN sagt bei mir
   products: lese alle Einträge, using WHERE (bei mir 212 Zeilen)

Ist ja auch klar: wenn eh' die meisten Einträge gelesen werden müssen, lohnt es sich nicht, einen Index zu nutzen.

prod_lists: zwei mögliche Indexe: using index, using where (2 Zeilen)

Tabelle "products" enthält bei mir 212 Zeilen, Tabelle "prod_lists" enthält 318 Zeilen, die Ergebnismenge liefert bei mir 191 Zeilen.

Freundliche Grüße

Vinzenz

[Chris]

Hallo Vinzenz,

dann erstelle einen Index für id_lists :-)

Oke, das hab ich nun gemacht ;)

SELECT

p.id_products                  -- ich gehe davon aus, dass Du an weiteren
                                   -- Spalten interessiert bist und nicht jeder
                                   -- Eintrag in der Tabelle products einen Eintrag
                                   -- in prod_lists haben muss :-)

Stimmt, das hast du durchschaut. Ich bin mir zudem sicher, dass du weißt, warum ich die Spalten nicht alle gelistet habe ;-)

FROM products p
WHERE p.id_products NOT IN (       -- wobei die id_products nicht in der Liste
    SELECT                         -- der id_products auftaucht
        pl.id_products
    FROM prods_lists pl            -- die in der Verknüpfungstabelle
    WHERE pl.id_lists IN (1, 2, 3) -- einer von ein paar ausgewählten Listen zugeordnet sind
)

>   
> Du hast Dein Subselect viel zu kompliziert aufgebaut :-)  
  
Auweia! Das ist ja wirklich erheblich einfacher. Danke für die Optimierung! :) Was hab ich mir da bloß gedacht...  
  
Leider bringt diese Query immer noch keine Geschwindigkeitsverbesserung mit. Auf meinem 200MHz Pentium dauert das gut 16 Sekunden.  
  
Schöne Grüße  
Christian

[Vinzenz Mai]

Hallo Christian,

SELECT

p.id_products                  -- ich gehe davon aus, dass Du an weiteren
                                   -- Spalten interessiert bist und nicht jeder
                                   -- Eintrag in der Tabelle products einen Eintrag
                                   -- in prod_lists haben muss :-)
FROM products p
WHERE p.id_products NOT IN (       -- wobei die id_products nicht in der Liste
    SELECT                         -- der id_products auftaucht
        pl.id_products
    FROM prods_lists pl            -- die in der Verknüpfungstabelle
    WHERE pl.id_lists IN (1, 2, 3) -- einer von ein paar ausgewählten Listen zugeordnet sind
)

  
Was sagt EXPLAIN dazu?  
  

> Leider bringt diese Query immer noch keine Geschwindigkeitsverbesserung mit. Auf meinem 200MHz Pentium dauert das gut 16 Sekunden.  
  
Wie lange benötigt das SELECT-Statement \_ohne\_ WHERE-Klausel?  
Wie lange benötigt die innere Abfrage, wenn Du sie allein abschickst?  
Welche Datenmenge liefert Dein Statement zurück?  
Kann es sein, dass Dein Hauptspeicher nicht ausreicht?  
  
Wie [Ilja](https://forum.selfhtml.org/?t=145654&m=946012) bereits anmerkte, sollte bei Deinem Datenbestand die Abfrage auch auf einem Pentium I mit 200 MHz schnell ausgeführt werden.  
  
  
Freundliche Grüße  
  
Vinzenz

[Christian]

Hallo Vinzenz,

erstaunlicherweise brauchen die Queries heute nur noch  knapp 6 Sekunden. Mit einem ausführlichen grep nebenher konnte ich sie wieder auf 15 Sekunden dehnen. Ich weiß zwar nicht, was für Prozesse da in den letzten Tagen gelaufen sind, aber jetzt ist zumindest klar, dass nicht MySQL schuld ist  :-)

Wie lange benötigt das SELECT-Statement _ohne_ WHERE-Klausel?

5.9 Sekunden

Wie lange benötigt die innere Abfrage, wenn Du sie allein abschickst?

0.2 Sekunden

Welche Datenmenge liefert Dein Statement zurück?

Knapp 30.000 Datensätze zu je 100B

Kann es sein, dass Dein Hauptspeicher nicht ausreicht?

Von den 48MB sind momentan knapp 6MB frei, während einer Query ändert sich da auch nicht viel.

Von daher bin ich nun eigentlich recht zufrieden mit den Ergebnissen, nur die 16 Sekunden kamen mir schon arg lang vor. Auf jeden Fall habe ich dabei wieder einiges gelernt! :-)

Danke für die Mühen und Grüße
Christian

[Ilja]

yo,

Ich möchte sämtliche Produkte haben mit Ausnahme von solchen, die in ganz bestimmten Listen enthalten sind. Die IDs dieser speziellen Listen habe ich, sagen wir mal 1, 2 und 3.

SELECT p.id_products
FROM products p
WHERE 0 = (SELECT COUNT(*)
           FROM prods_lists pl
           WHERE pl.id_lists IN (1,2,3)
           AND pl.id_products = p.id_products
          )
;

alternativ

SELECT p.id_products
FROM products p
WHERE NOT EXISTS (SELECT pl.id_lists
                  FROM prods_lists pl
                  WHERE pl.id_lists IN (1,2,3)
                  AND pl.id_products = p.id_products
                  )
;

Ilja

[Vinzenz Mai]

Hallo Ilja,

Ich möchte sämtliche Produkte haben mit Ausnahme von solchen, die in ganz bestimmten Listen enthalten sind. Die IDs dieser speziellen Listen habe ich, sagen wir mal 1, 2 und 3.

  

> SELECT p.id_products  
> FROM products p  
> WHERE 0 = (SELECT COUNT(*)  
>            FROM prods_lists pl  
>            WHERE pl.id_lists IN (1,2,3)  
>            AND pl.id_products = p.id_products  
>           )  
> ;  
>   
> alternativ  
>   
> SELECT p.id_products  
> FROM products p  
> WHERE NOT EXISTS (SELECT pl.id_lists  
>                   FROM prods_lists pl  
>                   WHERE pl.id_lists IN (1,2,3)  
>                   AND pl.id_products = p.id_products  
>                   )  

oder so :-)
Ich bin voreingenommen, ich finde meinen Vorschlag verständlicher; "intuitiver" möchte ich im Zusammenhang mit SQL lieber nicht gebrauchen.

Freundliche Grüße

Vinzenz

[Ilja]

yo,

oder so :-)
Ich bin voreingenommen, ich finde meinen Vorschlag verständlicher; "intuitiver" möchte ich im Zusammenhang mit SQL lieber nicht gebrauchen.

ja, übersichlicher betimmt. aber ich bin ein fan von der prüfung auf = 0. der NOT IN operator führt doch bei so manchen abfragen zu problemen, wo ich ein gleichheitszeichen einsetzen kann.

Ilja

[Chris]

Danke für deine Bemühungen!

Leider sind deine Subqueries korreliert. Sie brauchen bei mir knapp 30 Sekunden, sind also langsamer ;-(

Dass meine ursprüngliche Query viel zu kompliziert war, hat mir Vinzenz auch schon demonstriert.

Schöne Grüße
Christian

[Ilja]

yo,

Leider sind deine Subqueries korreliert. Sie brauchen bei mir knapp 30 Sekunden, sind also langsamer ;-(

ich benutze korrelierte abfragen jeden tag mehrfach über mehrere tabellen, die millionen einträge haben. wenn diese bei dir zu langsam sind, dann hast du keine geeigneten indexe angelegt, wie der fleißige Vinz schon darauf hingewiesen hat. die abfrage mit deinem wirklichen kleinen datenbestand sollte ratz fatz gehen.

was du brauchst ist auf jeden fall ein index in der tabelle products auf die spalte id_products. desweiteren einen zusammengesetzten index auf die tabelle id_lists mit den spalten id_products, id_lists. es kann sein, dass du die beiden bedingungen in der where klausel in einer anderen reihenfolge schreiben musst, je nachdem welches dbms du benutzt und welche version. aber wenn die indexe greifen, dann kann deine abfrage keine sekunden mehr dauern, auch wenn sie korreliert sind.

Ilja

[Christian]

was du brauchst ist auf jeden fall ein index in der tabelle products auf die spalte id_products. desweiteren einen zusammengesetzten index auf die tabelle id_lists mit den spalten id_products, id_lists. es kann sein, dass du die beiden bedingungen in der where klausel in einer anderen reihenfolge schreiben musst, je nachdem welches dbms du benutzt und welche version. aber wenn die indexe greifen, dann kann deine abfrage keine sekunden mehr dauern, auch wenn sie korreliert sind.

Ok, das merke ich mir fürs nächste Mal. Die Indizes sind nun richtig gesetzt und die Query dauert nur noch knapp 6 Sekunden. Irgendwelche Prozesse hatten die Queries in die Länge gezogen. Ich weiß nur leider nicht welche. 6 Sekunden braucht es auch um eine Liste mit sämtlichen id_products ausgeben zu lassen. Also denke ich, dass es nichts mehr zu optimieren gibt.

Danke für deine Hilfe!
Christian