Query optimieren / mysql 5

[Vinzenz Mai]

Hallo,

im Nachgang an diesen Thread im Mai 1009 habe ich meine Eintragsstruktur über Nested Sets wie in diesem Tutorial beschrieben, abgebildet.

das Tutorial auf MySQLs Seiten zeigt sehr mäßiges SQL. Ich fand's wenig empfehlenswert.

Ein wenig Sorge über die eventuell hohe Performancelastigkeit hatte ich letztes Jahr ja schon, aber es kam keine Antwort und wenn das dann schon in mysql.com als Lösung angeboten wird, wirds schon passen. Dachte ich mir.

Nun, knapp 1,5 Jahre und etwas über 2000 Nodes später, werde ich eines Besseren belehrt und muss mich (und über dieses Forum auch Euch) um eine Lösung folgenden Problemes bemühen:

Die Tabelle:

node_id|root_id|payload|lft|rgt |

1      |2009   |2009   |1  |1966|
2      |2009   |3611   |2  |7   |
3      |2009   |3614   |3  |6   |

(Die root-id ist immer die Jahreszahl, Payload des rootpostings ist gleich der Jahreszahl, die anderen Payloadeinträge entsprechenden der Thread-ID).

Und zu guter Letzt, die Abfrage oben soll alle Kinder und Kindeskinder des Threads 5895 herausfinden. Das funktioniert auch tadellos, wenn nicht 2000 Nodes und 4000 Threads vorhanden sind, sondern nur die Hälfte.

Die dazugehörige Query:

SELECT s.payload, m.spalte5,m.spalte6
FROM 000node v, 000node s, 000node s_id, threads m
WHERE s.lft BETWEEN v.lft AND v.rgt
AND s.payload = m.ID
AND m.delete != 1
AND s.root_id =  "2010"
AND s_id.payload = "5895"
AND s.lft BETWEEN s_id.lft AND s_id.rgt
GROUP BY s.lft

  

> Ehrlich gesagt hoffe ich darauf, die Query selber so abändern zu können, dass sie etwas performanter wird.  
  
warum der dreifache Selfjoin, dazu noch zweimal als Thetajoin, d.h. mit einer Nichtgleichheitsbedingung?  
  
Fangen wir einfach mal an:  
  
a) Ermitteln wir die node\_id des Threads  5895:  
  
~~~sql
SELECT  
    s.node_id  
FROM  
    000node s  
WHERE  
    s.payload = 5895  

Anmerkungen zu meinem Code:

1. Zahlen müssen in SQL nicht in Anführungszeichen gesetzt werden.
      In manchen SQL-Dialekten ist das sogar ein Fehler.
2. Wenn man schon Anführungszeichen verwendet, so sollte man sich
      unbedingt auf die einfachen Anführungszeichen beschränken, weil
      diese *immer* gehen, wenn sie gehen. Die meisten SQL-Dialekte
      erlauben *keine* doppelten Anführunszeichen, um Zeichenketten zu
      begrenzen.
3. Es wäre eine gute Idee, wenn über die Spalte payload ein Index läge.

b) Ermitteln wir nun den gesamten Baum unter einem gegebenen Knoten
   (gemäß Tutorial):

SELECT  
    node.payload  
FROM  
    000node parent  
INNER JOIN  
    000node node  
ON  
    node.lft BETWEEN parent.lft AND parent.rgt  
WHERE  
    parent.payload = 5895  
ORDER BY  
    node.lft;  

Anmerkungen:

1. den impliziten Join habe ich durch einen expliziten Join ersetzt.
2. payload ist für mich das Gegenstück zu name im Tutorial.
3. Ich habe parent zuerst notiert, um dem Query-Optimizer gegebenfalls
      auf die Sprünge zu helfen.
4. EXPLAIN kann helfen zu erkennen, ob der Index auf parent.payload
      genutzt werden kann, um von vornherein die Datenmenge einzuschränken.
5. Möchtest Du mit der Einschränkung root_id = 2010 die Daten auf das
      Jahr 2010 einschränken? Was ist mit Threads, die über einen Jahreswechsel
      gehen?

Wie die Verknüpfung zur Tabelle thread erfolgen kann, das kann ich Dir
nicht sagen, weil Du diese Tabelle nicht vorgestellt hast und auch nicht
erklärt hast, welche Informationen aus dieser Tabelle Du zeigen willst.

Auf alle Fälle solltest Du den zweifachen Selfjoin vermeiden und nur auf
einen einfachen Selfjoin reduzieren können, was sich erheblich auf die
Performance auswirken sollte.

Freundliche Grüße

Vinzenz