left join optimieren

[Joachim]

Hi,

Meine bescheidenen SQL-Kenntnisse brauchen etwas Support. 3 Tabellen,  eine mit Artikeln (a), eine mit Benutzerdaten (b) und eine mit Kommentaren (c). Ich möchte alle Artikel, die Autorendaten und die Anzahl der Kommentare zu einem Artikel. Mein alter Ansatz war wie folgt:

"select a.id, a.article\_title, a.article\_text, a.article\_author\_id, a.date, b.email, b.nick, COUNT(c.comment\_article\_id) from
            table\_a as a,
            table\_b as b LEFT JOIN
            table\_c as c ON
            a.id=c.comment\_article\_id
        where a.article\_author\_id=b.id and a.blog\_name='blogname'
        GROUP BY a.id
        order by a.id desc LIMIT 0, 10;

Wir schwant dunkel, das dieser Ansatz nicht optimal ist, denn wenn ich meine Quellen korrekt interpretiere, muss b jedesmal komplett gescannt werden um die Autorendaten aus b zu ermitteln. Ist es wie folgt besser gelöst (funktionieren tut es)?

"select a.id, a.article\_title, a.article\_text, a.article\_author\_id, a.date, b.email, b.nick, COUNT(c.comment\_article\_id) from
            table\_a as a LEFT JOIN
            table\_b as b ON
            (a.article\_author\_id=b.id) LEFT JOIN
            table\_c as c ON
            (a.id=c.comment\_article\_id)
        where a.blog\_name='blogname'
        GROUP BY a.id
        order by a.id desc LIMIT 0, 10;

Falls gut - weiter optimierbar?

Gruesse, Joachim

[Vinzenz Mai]

Hallo Joachim,

Wir schwant dunkel, das dieser Ansatz nicht optimal ist,

ist er meiner Meinung nach auch nicht. Er "funktioniert" wegen der Fehlertoleranz bzw. "Optimierung" von MySQL. Jedes andere DBMS, das ich kenne, gibt einen SQL-Fehler zurück. Ich finde das Verhalten der anderen DBMS gut.

denn wenn ich meine Quellen korrekt interpretiere, muss b jedesmal komplett gescannt werden um die Autorendaten aus b zu ermitteln. Ist es wie folgt besser gelöst (funktionieren tut es)?

Welche Quelle? Hast Du EXPLAIN verwendet?

[...] from
            table\_a as a LEFT JOIN
            table\_b as b ON
            (a.article\_author\_id=b.id) LEFT JOIN

vorher hattest Du einen (impliziten) INNER JOIN, warum machst Du daraus einen OUTER JOIN?

FROM table_a a  
INNER JOIN table_b b  
ON a.article_author_id = b.id

gefiele mir besser. Jedem Beitrag ist ein existierender Autor zugeordnet => INNER JOIN. Nicht jeder Beitrag wurde bereits kommentiert => OUTER JOIN.

table\_c as c ON
            (a.id=c.comment\_article\_id)
        where a.blog\_name='blogname'
        GROUP BY a.id
        order by a.id desc LIMIT 0, 10;

Falls gut - weiter optimierbar?

Meiner persönlichen Meinung nach nicht gut. Es fehlt die Gruppierung nach folgenden Spalten:

a.article_title,
    a.article_text,
    a.article_author_id,
    a.date,                -- Datentyp, hier ist das Quoten nötig
    b.email,
    b.nick

Zu Deinem Glück sind diese Daten alle direkt von a.id abhängig und somit für jeden Eintrag gleich, so dass MySQL nicht unvorhersagbare Daten zurückliefert. Ich halte von dieser "Optimierung" von GROUP BY durch MySQL nichts. Wenn man eine Aggregatsfunktion verwendet, so muss nach allen nicht aggregierten Spalten gruppiert werden - nach diesem strengen Grundsatz verfahren alle anderen mir bekannten DBMS. Weiterhin verzichte ich auf Spaltennamen wie "date", die eine spezielle Behandlung erfordern. [1]

Schau' Dir das Ergebnis von EXPLAIN an.

Freundliche Grüße

Vinzenz

[1] Backtickitis macht meiner Meinung nach SQL-Anweisungen in MySQL nicht lesbarer genausowenig wie Backslashitis PHP- oder Javascript-Statements.

[Joachim]

Hi Vinzenz,

vielen Dank für Deine Hinweise, ich habe sie gleich verwendet. Leider ist mein SQL-Wissen wirklich dünn, ich sehe, ich komme um ein gaaanz dickes Buch nicht länger herum ;-)

Explain gibt mir jetzt sowas aus - aber mir ist nicht ganz klar, inwiefern ich das zum optimieren nutzen kann.
table type possible_keys key  key_len ref  rows
a     ALL  NULL          NULL NULL    NULL 6    Using where; Using temporary; Using filesort
b     ALL  id            NULL NULL    NULL 3    Using where
c     ALL  NULL          NULL NULL    NULL 2

... date als name war natuerlich ungeschickt...

Gruesse, Joachim

[Vinzenz Mai]

Hallo

Explain gibt mir jetzt sowas aus - aber mir ist nicht ganz klar, inwiefern ich das zum optimieren nutzen kann.
table type possible_keys key  key_len ref  rows
a     ALL  NULL          NULL NULL    NULL 6    Using where; Using temporary; Using filesort

ohje. Da kannst Du optimieren ohne Ende. Siehe die Handbuchseite zu EXPLAIN.
Es scheint einfach zu sein. Deine id-Spalten identifizieren doch jeweils in ihren Tabellen einen Datensatz. Du solltest auf diese Spalte einen PRIMARY KEY setzen (setzt einen UNIQUE-Index, also einen eindeutigen Index) voraus. Das sollte gewaltig helfen.

b     ALL  id            NULL NULL    NULL 3    Using where
c     ALL  NULL          NULL NULL    NULL 2

Freundliche Grüße

Vinzenz

[Joachim]

Hi,

ohje. Da kannst Du optimieren ohne Ende. Siehe die Handbuchseite zu EXPLAIN.

Wie gesagt, das dicke Buch ;-)
Ich habe (das war in allen tabellen so) id-autoincrement-Spalten von Index auf Primary gesetzt. Explain sagt jetzt:
table type possible_keys key  key_len ref  rows Extra
a     ALL  NULL          NULL NULL    NULL 6  Using where; Using temporary; Using filesort
b     ALL  PRIMARY       NULL NULL    NULL 3  Using where
c     ALL  NULL          NULL NULL    NULL 2

Sprich fuer a und c hat er keine possible_keys. Aber in diesen Tabellen muessen ja auch Übereinstimmungen gefunden werden, die nicht unique sein können:
a.article_author_id = b.id
^^^^^^^^^^^^^^^^^^^
a.id = c.comment_article_id
       ^^^^^^^^^^^^^^^^^^^^

Naja, ist nur eine Spielwiese, habe noch viel Zeit zum optimieren.

Gruesse, Joachim

[Vinzenz Mai]

Hallo Joachim,

a     ALL  NULL          NULL NULL    NULL 6  Using where; Using temporary; Using filesort
b     ALL  PRIMARY       NULL NULL    NULL 3  Using where
c     ALL  NULL          NULL NULL    NULL 2

Sprich fuer a und c hat er keine possible_keys. Aber in diesen Tabellen muessen ja auch Übereinstimmungen gefunden werden, die nicht unique sein können:
a.article_author_id = b.id
^^^^^^^^^^^^^^^^^^^
a.id = c.comment_article_id
       ^^^^^^^^^^^^^^^^^^^^

diese Spalten solltest Du ebenfalls mit einem Index versehen :-)
Somit sollte der Optimierer in Tabelle a den Index der Spalte article_author_id finden und nutzen.

Welche MySQL-Version verwendest Du?

Schreibe mal die Query um:

SELECT  
    a.id,  
    a.article_title,  
    a.article_text,  
    a.article_author_id,  
    a.`date`,  
    b.email,  
    b.nick,  
    COUNT(c.comment_article_id)  
FROM table_b b  
INNER JOIN table_a a  
ON b.id = a.article_author_id  
LEFT JOIN c  
ON a.id = c.comment_article_id  
WHERE a.blog_name = 'blogname'  
GROUP BY  
    a.id,  
    a.article_title,  
    a.article_text,  
    a.article_author_id,  
    a.`date`,  
    b.email,  
    b.nick  
ORDER BY a.id DESC  
LIMIT 0, 10 

Indexe:
a.id PRIMARY KEY
a.article_author_id INDEX (nicht UNIQUE)

b.id PRIMARY KEY

c.comment_article_id INDEX (nicht UNIQUE

Beachte bitte, dass je Tabelle in einer Abfrage nur _ein_ Index genutzt werden kann. using filesort und using temporary ist beides schlecht, zusammen noch ungünstiger. Dort sollte man bei Optimierungen ansetzen.

Freundliche Grüße

Vinzenz

[Joachim]

Hi Vinzenz,

hab das mal umgebaut, Explain ergibt jetzt:
table type   possible_keys      key                key_len ref                        rows Extra
a     ALL    article_author_id  NULL               NULL    NULL                        6 Using where; Using temporary; Using filesort
c     ref    comment_article_id comment_article_id 4       a.id                    2 Using index
b     eq_ref PRIMARY            PRIMARY            4       a.article_author_id 1

using filesort und using temporary ist beides schlecht, zusammen noch ungünstiger.

Wird leider immer noch benutzt

Gruesse, Joachim

[Ilja]

yo,

Beachte bitte, dass je Tabelle in einer Abfrage nur _ein_ Index genutzt werden kann.

das ist zwar weit verbreitet, stimmt aber meiner meinung nach so nicht. ein dbms (optimierer) wird aus bestimmten abfragen, zum beispiel mit OR verknüpft, zwei abfragen draus machen und kann somit bei einer abfrage auch mehr als einen index nutzen.

Ilja