Query optimieren / mysql 5

[KlausStein]

Hallo,

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

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:

Bei einem select-Statement erhalte ich folgende Fehlermeldung:

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

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.

Die Tabelle:

node_id|root_id|payload|lft|rgt |
--------------------------------
1      |2009   |2009   |1  |1966|
--------------------------------
2      |2009   |3611   |2  |7   |
---------------------------------
3      |2009   |3614   |3  |6   |
---------------------------------
usw.

(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.

Ich verwende mysql5, wer hilft mir mal auf die Sprünge, wie ich die Abfrage so performant hinbekomme, dass mysql5 nicht meckert?

Grüße, KlausStein

[Matti Maekitalo]

Tach auch.

Ich verwende mysql5, wer hilft mir mal auf die Sprünge, wie ich die Abfrage so performant hinbekomme, dass mysql5 nicht meckert?

Hast du gängige Statements wie EXPLAIN schon ausprobiert?

Bis die Tage,
Matti

[KlausStein]

Hast du gängige Statements wie EXPLAIN schon ausprobiert?

Hi,

ehrlich gesagt nicht, weil ich das Ergebnis ohnehin nicht deuten kann.

Gruß, KlausStein

[Matti Maekitalo]

Tach auch.

Hast du gängige Statements wie EXPLAIN schon ausprobiert?
ehrlich gesagt nicht, weil ich das Ergebnis ohnehin nicht deuten kann.

Hindert dich etwas daran, das zu ändern (und damit meine ich die fehlende Deutungsmöglichkeit) und das Ergebnis, sowohl von EXPLAIN als auch von deinen Interpretationsversuchen hier zu posten?

Bis die Tage,
Matti

[Ilja]

moin,

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

  
das ist eine fehlerhafte abfrage, die nur mysql verarbeiten würde. jedes andere dbms würde eine fehlermeldung ausgeben. des weiteren benutzt du die implizite join schreibweise, benutze immer die explizite. es gibt keinen vernüftigen grund sie nicht zu benutzen. "delete" als spaltennamen zu verwenden ist ebenfalls keine gute idee, ist ein resaviertes wort. auch namen wir "lft" sind wenig aussagekräftig. zu guter letzt eine frage,handelt es sich hier um tabellen "000node" oder um views ?  
  
Ilja

[dedlfix]

Hi!

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

>   
> das ist eine fehlerhafte abfrage, die nur mysql verarbeiten würde. jedes andere dbms würde eine fehlermeldung ausgeben.  
  
Das würde ich jetzt mal nicht als das Hauptproblem ansehen.  
  

> des weiteren benutzt du die implizite join schreibweise, benutze immer die explizite. es gibt keinen vernüftigen grund sie nicht zu benutzen.  
  
Eher verwundert mich, dass er nur eine Join-Bedingung aber vier Tabellen/Aliase hat. Oder zählen die beiden BETWEEN-Bedingunen zu den JOIN-Bedingungen und nicht zu den datensatzeinschränkenden? Das Hauptproblem ist ja, dass er hier wohl irgendwelche riesigen kartesischen Produkte bekommt, weil er nicht richtig verknüpft und/oder einschränkt.  
  

> auch namen wir "lft" sind wenig aussagekräftig.  
  
Im Kontext Nested Sets ist das schon ausreichend. Ich würde das sogar auf L und R beschränken. Und wenn du so willst, wären die Aliasnamen ja auch nicht aussagekräftig. Aber das ist auch ein unwichtiger Nebenkriegsschauplatz.  
  

> zu guter letzt eine frage,handelt es sich hier um tabellen "000node" oder um views ?  
  
Wofür ist diese Unterscheidung wichtig? Meinst du, wenn es eine View ist, dass die vielleicht selbst durch einen Join eine Menge Daten generiert, und man auch noch die Definition der View kennen muss?  
  
Wie wäre es noch mit der Frage, was für eine Aufgabenstellung zu diesem Lösungsversuch geführt hat. Denn ohne die zu kennen, wird man eher schwer zu einer richtigen Lösung kommen.  
  
  
Lo!

[dedlfix]

Hi!

Wie wäre es noch mit der Frage, was für eine Aufgabenstellung zu diesem Lösungsversuch geführt hat. Denn ohne die zu kennen, wird man eher schwer zu einer richtigen Lösung kommen.

Oh, stand ja doch da, hatte ich schon wieder vergessen und nicht nochmal nachgesehen.

Lo!

[Ilja]

moin,

das ist eine fehlerhafte abfrage, die nur mysql verarbeiten würde. jedes andere dbms würde eine fehlermeldung ausgeben.

Das würde ich jetzt mal nicht als das Hauptproblem ansehen.

wenn schon verbessern, dann gleich richtig.

des weiteren benutzt du die implizite join schreibweise, benutze immer die explizite. es gibt keinen vernüftigen grund sie nicht zu benutzen.

Eher verwundert mich, dass er nur eine Join-Bedingung aber vier Tabellen/Aliase hat. Oder zählen die beiden BETWEEN-Bedingunen zu den JOIN-Bedingungen und nicht zu den datensatzeinschränkenden? Das Hauptproblem ist ja, dass er hier wohl irgendwelche riesigen kartesischen Produkte bekommt, weil er nicht richtig verknüpft und/oder einschränkt.

ja, und deswegen auch die explizite schreibweise, die bringt sowas immer sehr schnell ans tageslicht.

auch namen wir "lft" sind wenig aussagekräftig.

Im Kontext Nested Sets ist das schon ausreichend. Ich würde das sogar auf L und R beschränken. Und wenn du so willst, wären die Aliasnamen ja auch nicht aussagekräftig. Aber das ist auch ein unwichtiger Nebenkriegsschauplatz.

naja, bei aliasnamen kann ichmir immer noch die zugehörige tabelle als sprechendeobjekt anschauen, aber bei den spalten kann ich es ja nicht mehr auflösen. für jemanden der dann diese abfragen nachbearbeiten muss, machen solche bezeichnungne es nur untöig schwieriger.

zu guter letzt eine frage,handelt es sich hier um tabellen "000node" oder um views ?

Wofür ist diese Unterscheidung wichtig? Meinst du, wenn es eine View ist, dass die vielleicht selbst durch einen Join eine Menge Daten generiert, und man auch noch die Definition der View kennen muss?

was auch immer, wenn es zu performance problemen kommt, dann will ich schon wissen, was in dereventuell vorhandenen view steckt.

Ilja

[Vinzenz Mai]

Hallo Ilja,

Eher verwundert mich, dass er nur eine Join-Bedingung aber vier Tabellen/Aliase hat. Oder zählen die beiden BETWEEN-Bedingunen zu den JOIN-Bedingungen und nicht zu den datensatzeinschränkenden? Das Hauptproblem ist ja, dass er hier wohl irgendwelche riesigen kartesischen Produkte bekommt, weil er nicht richtig verknüpft und/oder einschränkt.

ja, und deswegen auch die explizite schreibweise, die bringt sowas immer sehr schnell ans tageslicht.

Du sagst ja gerne "Joins sind böse.". Ist dies hier nicht der Fall - und vielleicht nur MySQLs hoher Verbreitung und ehemaliger Subselect- und SP-Unfähigkeit geschuldet?

Wenn ich mir das von mir umgeschriebene

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;  

ansehe, so ist es in Wirklichkeit gar kein Join, sondern durch folgende Statements ersetzbar:

SELECT  
   lft,  
   rgt  
FROM  
   000node  
WHERE  
   <bedingungen, die Ausgangsknoten eindeutig identifizieren>;

Wenn es genau einen Datensatz gibt, fülle die Variablen lower und upper mit den Werten aus lft bzw. rgt. und gebe anschließend das Resultset von

SELECT  
    kommagetrennte,  
    liste,  
    gewünschter,  
    spalten  
FROM  
    000node  
WHERE  
    lft BETWEEN upper AND lower

zurück, was über eine SP problemlos machbar sein sollte. MySQLs SELECT ... INTO unterstützt das Füllen von Variablen, die man anschließend in einem serverseitigen Prepared Statement nutzen könnte (kein dynamisches SQL in einer SP in MySQL).

alternativ:

SELECT  
    node.payload  
FROM  
    000node node  
WHERE  
    node.lft BETWEEN (SELECT  
                          parent.lft  
                      FROM  
                          000node parent  
                      WHERE  
                          parent.payload = 5895  
                      )  
             AND     (SELECT  
                          parent.rgt  
                      FROM  
                          000node parent  
                      WHERE  
                          parent.payload = 5895  
                      )  

in der Hoffnung, dass der Optimizer merkt, dass er die Subselects nur einmal auszuführen braucht (und zwei Subselects, wo mir eines lieber wäre, aber mir fällt gerade nichts ein, womit ich dieses umgehen könnte). Andere DBMS kennen ja eine WITH-Klausel, die man für genau solche Zwecke wunderbar nutzen könnte.

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo,

SELECT

node.payload
FROM
    000node node
WHERE
    node.lft BETWEEN (SELECT
                          parent.lft
                      FROM
                          000node parent
                      WHERE
                          parent.payload = 5895
                      )
             AND     (SELECT
                          parent.rgt
                      FROM
                          000node parent
                      WHERE
                          parent.payload = 5895
                      )

  

> in der Hoffnung, dass der Optimizer merkt, dass er die Subselects nur einmal auszuführen braucht (und zwei Subselects, wo mir eines lieber wäre, aber mir fällt gerade nichts ein, womit ich dieses umgehen könnte).  
  
mit einem Join auf ein Subselect könnte ich es in der Tat umgehen :-)  
  
~~~sql
SELECT  
    node.payload  
FROM  
    000node node  
INNER JOIN (  
    SELECT  
        lft,  
        rgt  
    FROM  
        000node  
    WHERE  
        payload = 5895  
) parent  
ON  
    node.lft BETWEEN parent.lft AND parent.rgt  

Wiederum geht es ausschließlich darum, dem Query-Optimizer "von Hand" beizubringen, dass er aus "parent" nur einen einzigen Datensatz zu berücksichtigen braucht. Dieser Datensatz solle über einen Index effizient zu finden sein und die Auswahl der zurückzuliefernden Datensätze der Gesamtquery sollte mit Hilfe eines Indexes über die Spalte lft ebenfalls sehr performant erfolgen.

Freundliche Grüße

Vinzenz

[Ilja]

moin Vinzenz,

Du sagst ja gerne "Joins sind böse.". Ist dies hier nicht der Fall - und vielleicht nur MySQLs hoher Verbreitung und ehemaliger Subselect- und SP-Unfähigkeit geschuldet?

das kann sehr gut sein. aber unabhängig davon, obe sie in dem spezifischen fall gut oder schlecht sind, wenn man sie einsetzt, dann mit expliziter schreibweise. ;-)

Ilja

[Blaubart]

Tach.

das ist eine fehlerhafte abfrage, die nur mysql verarbeiten würde. jedes andere dbms würde eine fehlermeldung ausgeben.

Das würde ich jetzt mal nicht als das Hauptproblem ansehen.

wenn schon verbessern, dann gleich richtig.

Dann mach's doch bitte! Was genau ist denn falsch und wie macht man's richtig? Ich bin nach Deiner Anmerkung noch genauso schlau wie vorher.

[dedlfix]

Hi!

das ist eine fehlerhafte abfrage, die nur mysql verarbeiten würde. jedes andere dbms würde eine fehlermeldung ausgeben.
Was genau ist denn falsch und wie macht man's richtig?

Offensichtlich weißt du das nicht, aber dieses Verhalten MySQLs und die potentiellen Probleme, die daraus resultieren, wurden schon so oft beschrieben, dass man nicht immer Lust hat, das schon wieder zu beschreiben. "MySQL GROUP BY" dürfte vermutlich eine Menge Fundstellen zu Tage fördern. Konkret geht es darum, wenn eine Gruppierung vorgenommen wurde, dann ergibt das eine Zusammenfassung - aus meheren Datensätzen entsteht einer in der Ergebnismenge. Eine Aggregatefunktion ist kein Problem, denn die errechnet beispielsweise einen Durchschnittswert, eine Summe oder einen Extremwert über alle Datensätze der Gruppierung, ergibt somit ein eindeutiges Ergebnis. Selektierst du nach Feldern, die nicht in der Gruppierung vorkommen, - die Daten welchen Datensatzes sollen dann in der Ergebnismenge landen? Alle DBMS außer MySQL weisen eine Selektion von nicht gruppierten Feldern zurück, weil diese Frage nicht eindeutig geklärt werden kann. MySQL streubt sich nicht, nimmt jedoch irgendeinen Datensatz aus der Gruppierung. Das funktioniert nur dann zuverlässig, wenn du Redundanzen in der Tabelle hast (was oftmals auf Normalisierungspotential hat). Ein Liste der Bestellungen, gruppiert über die Kundennummer, soll den Kundennamen und die Summe ausweisen. Normalisiert hat der Name nichts in dieser Tabelle verloren. Aber da der Name pro Kundennummer sehr wahrscheinlich immer gleich ist, ist es egal, welcher Datensatz davon genommen wird.

Lo!

[Blaubart]

Tach.

das ist eine fehlerhafte abfrage, die nur mysql verarbeiten würde. jedes andere dbms würde eine fehlermeldung ausgeben.
Was genau ist denn falsch und wie macht man's richtig?

Offensichtlich weißt du das nicht, aber dieses Verhalten MySQLs und die potentiellen Probleme, die daraus resultieren, wurden schon so oft beschrieben, dass man nicht immer Lust hat, das schon wieder zu beschreiben. "MySQL GROUP BY" dürfte vermutlich eine Menge Fundstellen zu Tage fördern.

Na immerhin weiß ich jetzt, daß es in diesem Fall um das GROUP BY geht. Wenigstens *das* als Stichwort wäre das ja auch in Iljas Bemerkung hilfreich gewesen. Möglicherweise sogar garniert mit einem Verweis auf eine der vielen Stellen, an denen das Thema erörtert wurde.

[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

[Rainer]

Hallo,

.... im Mai 1009 habe ich meine Eintragsstruktur ....
            ^^^^

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

Stammt ja auch alles aus Urzeiten ;-)

Gruß Rainer

[KlausStein]

Hallo Vinzenz,

Anmerkungen zu meinem Code:

Vielen Dank für die Anmerkungen. Da sind viele nützliche Tips drin, die ich mir merken werde.

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

Ich hätte fast gesagt, dass es wunderbar funktioniert, wenn ich nicht justament ein falsches Ergebnis verzeichnet hätte.

Es wurde mir ein Child genannt, das definitiv ein  Nicht-Child ist.

In dieser Query:

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

erhalte ich als Ergebnis:

4303
5290

Aber die ID 5290 ist definitiv kein Child von 4303.

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?

Da sollte ich wirklich  nochmal drüber nachdenken, hm?

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.

Aber ich habe gesagt, wie sie mit der Node-Tabelle verknüpft ist. Payload ist gleich der ID in der Thread-Tabelle. Aber das krieg ich selber über einen Join hin.

Mehr beunruhigt mich, dass ich ein falsches Ergebnis vorliegen habe.

Wie kann ich das anhand der Tabelle per Sichtung verifizieren?

Freundlichen Gruß, KlausStein

[KlausStein]

Ich habe meine Node-Tabelle nach diesem Tutorial aufgebaut.

Grüße, KlausStein

[Vinzenz Mai]

Hallo,

Vielen Dank für die Anmerkungen. Da sind viele nützliche Tips drin, die ich mir merken werde.

Es wurde mir ein Child genannt, das definitiv ein  Nicht-Child ist.

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

>   
> erhalte ich als Ergebnis:  
>   
> 4303  
> 5290  
>   
> Aber die ID 5290 ist definitiv kein Child von 4303.  
  

> Mehr beunruhigt mich, dass ich ein falsches Ergebnis vorliegen habe.  
> Wie kann ich das anhand der Tabelle per Sichtung verifizieren?  
  
Schau Dir an, wie die betreffenden Knoten aussehen:  
  
~~~sql
SELECT  
    payload,  
    lft,  
    rgt  
FROM  
    000node  
WHERE  
    payload IN (4303, 5290)  

Schau Dir den lft-Wert des Kindknotens und die Grenzen des Elterknoten an.
Ob ich den Join implizit in der WHERE-Klausel oder explizit in der JOIN-Klausel schreibe ist beim INNER JOIN für die Ergebnismenge nicht relevant.

Freundliche Grüße

Vinzenz

[KlausStein]

Hallo Vinzenz,

Schau Dir an, wie die betreffenden Knoten aussehen:

SELECT

payload,
    lft,
    rgt
FROM
    000node
WHERE
    payload IN (4303, 5290)

  
Hallo Vinzenz,  
  
das habe ich gemacht und das Ergebnis war enttäuschend.  
  
payload  lft   	rgt  
4303 	1318 	1319  
5290 	1318 	1319  
  
  
Das heißt, wir diskutieren gerade unter völlig verzerrter Ausgangslage und ich muss diese erstmal in Ordnung bringen und hoffe, Du greifst mir dabei ein wenig unter die Arme. Ich tue mein Bestes dazu, bin aber garde erstmal etwas gefrsutet, weil mein Nested Set entweder bei der Anlage bzw. bei dessen Fortführung einen Fehler beinhaltet!  
  
Glücklicherweise habe ich mich nicht ausschließlich zu meinem Nested Set verlassen und habe redundant in meiner main-tabelle den parent-Eintrag mitgeschleppt. Vielleicht gelingt es, den Fehlker zu finden und die node-tabelle neu aufzubauen?  
  
Zum Hintergrund: Stell Dir eine Art schwarzes Brett vor, bei dem User sich gegenseitig auf verschiedene Art schreiben oder auf sich reagieren können.  
  
Das ist meine Haupttabelle "main".  
  
MainID|User|Datum    |...|  Eigennummer  |    parent    |Status|...|  
\------+----+---------+---+---------------+--------------+------+---|  
1     +10  +127...   +...+Status-jahr-lfd+Stat-jahr-lfd-+ X1   +...|  
\------+----+---------+---+---------------+--------------+------+---|  
2     +7   +127...   +...+Status-jahr-lfd+Stat-jahr-lfd-+ X4   +...|  
\-------------------------------------------------------------------|  
usw.  
  
Leider führe ich das Datum als timestamp mit. Das Programm ist schon etwas älter, das haben früher wohl viele so gemacht.  
  
Die Eigennummer setzt sich aus dem Status, der 2-stelligen Jahreszahl und einer fortlaufenden Nummer zusammen. Der Status selber besagt nur die Art der Kommunikation.  
  
Dann gibts noch eineige andere Spalten, die aber erstmal weniger interessant sein dürften.  
  
Das Nested Set benötige ich, um eine Art Baumstruktur der Kommunikation herstellen zu können.  
  
Eintrag:  
  
Zum Eintragen rufe ich eine Funktion mit 2 Parametern auf. Einmal der MainID und, falls vorhanden einer ReferenzID, also der MainID eines parent.  
  
In der funktion prüfe ich bei Vorhandensein einer ReferenzID erstmal, ob es einen Vorgang in der node-tabelle mit diesen Daten gibt.  
Ist das der Fall, besorge ich mir die Referenzdaten und mache den Eintrag:  
  
~~~sql
  
$query_node_referenz="select  root_id,lft,rgt  FROM ".tableprefix."node WHERE payload ='$ReferenzID'";  
$result_node_referenz=mysql_query($query_node_referenz);  
if ($result_node_referenz==FALSE)  
     {  
          error(mysql_error(),'','',''); // ja, ich weiß, was nun kommt...  
     }  
$row_node_referenz=mysql_fetch_row($result_node_referenz);  
$V_ROOT_ID=$row_node_referenz[0];  
$V_LFT=$row_node_referenz[1];  
$V_RGT=$row_node_referenz[2];  
  
  
// Und jetzt der neue Eintrag  
$result10=mysql_query("LOCK TABLES '".tableprefix."node' WRITE");  
  
$query_update1="UPDATE ".tableprefix."node  
   SET lft      =  lft + 2  
 WHERE root_id  =  ".$V_ROOT_ID."  
   AND lft      >  ".$V_RGT."  
   AND rgt      >= ".$V_RGT."";  
$result_update1=mysql_query($query_update1);  
  
$query_update2="UPDATE ".tableprefix."node  
   SET rgt      =  rgt + 2  
 WHERE root_id  =  ".$V_ROOT_ID."  
   AND rgt      >= ".$V_RGT."";  
$result_update2=mysql_query($query_update2);  
  
$query2="  
INSERT INTO ".tableprefix."node ( root_id, payload, lft, rgt )  
          VALUES ( $V_ROOT_ID, $MainID, $V_RGT, $V_RGT + 1 )";  
$result12=mysql_query($query2);  
$result13=mysql_query("UNLOCK TABLES");  

Nun der Fall, dass keine ReferenzID übergeben wurde:

Dann scahue ich zuerst nach, ob im lfd. Jahr schon ein Eintrag existiert oder nicht.

Fall 1 (es existiert noch keine):

  
  
$result14=mysql_query("LOCK TABLES '".tableprefix."node' WRITE");  
$query="  
  
INSERT INTO ".tableprefix."node ( payload, lft, rgt )  
          VALUES ( '$Jahr', '1', '2' )";  
$result22=mysql_query($query);  
  
$last_insert_id= mysql_result(mysql_query("SELECT MAX(node_id) FROM ".tableprefix."node"), 0);  
  
$query_update1="UPDATE ".tableprefix."node  
   SET root_id = ".$Jahr."  
 WHERE node_id = ".$last_insert_id."";  
$result_update1=mysql_query($query_update1);  
  
 $query_update2=" UPDATE ".tableprefix."node  
   SET lft      =  lft + 2  
 WHERE root_id  =  ".$Jahr."  
   AND lft      >  2  
   AND rgt      >= 2";  
$result_update2=mysql_query($query_update2);  
  
 $query_update3="  
UPDATE ".tableprefix."node  
   SET rgt      =  rgt + 2  
 WHERE root_id  =  ".$Jahr."  
   AND rgt      >= 2";  
$result_update3=mysql_query($query_update3);  
  
$query2="  
INSERT INTO ".tableprefix."node ( root_id, payload, lft, rgt )  
          VALUES ( $Jahr, $MainID, 2, 3 )";  
		  
$result23=mysql_query($query2);  
$result15=mysql_query("UNLOCK TABLES");  

Fall 2 (es gibt schon > 1 Eintrag in diesem Jahr):

  
// Hierzu erstmal die Referenzdaten des Leereintrag des lfd. Jahres besorgen  
$query_node_referenz="select root_id,lft,rgt  FROM ".tableprefix."node WHERE payload =\"$Jahr\"";  
$result_node_referenz=mysql_query($query_node_referenz);  
if ($result_node_referenz==FALSE)  
     {  
          error(mysql_error(),'','','');  
     }  
$row_node_referenz=mysql_fetch_row($result_node_referenz);  
$V_ROOT_ID=$row_node_referenz[0];  
$V_LFT=$row_node_referenz[1];  
$V_RGT=$row_node_referenz[2];  
  
//################################################  
  
  
// Und jetzt der neue Eintrag  
$result16=mysql_query("LOCK TABLES '".tableprefix."node' WRITE");  
  
$query_update1="UPDATE ".tableprefix."node  
   SET lft      =  lft + 2  
 WHERE root_id  =  ".$V_ROOT_ID."  
   AND lft      >  ".$V_RGT."  
   AND rgt      >= ".$V_RGT."";  
$result_update1=mysql_query($query_update1);  
  
$query_update2="  UPDATE ".tableprefix."node  
   SET rgt      =  rgt + 2  
 WHERE root_id  =  ".$V_ROOT_ID."  
   AND rgt      >= ".$V_RGT."";  
  
$result_update2=mysql_query($query_update2);  
  
$query2="  
INSERT INTO ".tableprefix."node ( root_id, payload, lft, rgt )  
          VALUES ( $V_ROOT_ID, $VorgangsID, $V_RGT, $V_RGT + 1 )";  
$result18=mysql_query($query2);  
$result19=mysql_query("UNLOCK TABLES");  
  

Ich hoffe, ich konnte die Vorgangsweise knapp genug schildern, um keinen damit zu überfordern und ausführlich genug, um eventuelle Fehler aufzudecken.

Übrigens lösche ich keine Datensätze aus der node-tabelle heraus. Die berücksichtige ich später in den Abfragen über ein "delete-flag", dass in der main-Tabelle vorliegt.

--------------------------------------------

Siehst Du in der obigen Vorgehensweise den Grund für die ja zweifelsfrei vorhandenen Fehleinträge?

Weil, wenn das alles soweit korrekt wäre, müsste der Fehler in den übergebenen Parametern liegen.

Bis hierher erstmal, danke fürs "Miträtzeln" und freundliche Grüße.

KlausStein

[KlausStein]

//################################################

// Und jetzt der neue Eintrag

$query2="
INSERT INTO ".tableprefix."node ( root_id, payload, lft, rgt )
          VALUES ( $V_ROOT_ID, $VorgangsID, $V_RGT, $V_RGT + 1 )";
$result18=mysql_query($query2);
$result19=mysql_query("UNLOCK TABLES");

[/code]

Ich hoffe, ich konnte die Vorgangsweise knapp genug ...

Das sollte natürlich nicht $VorgangsID, sondern $MainID heißen.
Ich habs vergessen, hier im Beitrag umzubenennen. Im Hauptscript arbeite ich mit $VorgangsID, was aber nichts anderes ist, als die $MainID. Ich dachte, im Beitrag würde das Umbenennen für etwas mehr Transparenz sorgen.

Grüße, KlausStein

[Vinzenz Mai]

Hallo Klaus,

Schau Dir an, wie die betreffenden Knoten aussehen:
das habe ich gemacht und das Ergebnis war enttäuschend.

das Ergebnis habe ich in etwa erwartet.

payload  lft    rgt
4303 1318 1319
5290 1318 1319

Verschaffe Dir einen schnellen Überblick, ob dieses Problem mehrfach vorliegt:

  
-- suche lft-Werte, die mehrfach auftreten - was ja ein Fehler ist.  
SELECT  
    COUNT(lft)  
FROM  
    000node  
HAVING COUNT(lft) > 1  

Eintrag:

viele Nested-Set-Tutorials sehen sich sehr ähnlich und gehen oberflächlich über das Eintragen hinweg. Sie verwenden zwar Locking, aber kein ausreichendes Locking und bauen so ein Time-of-check-to-time-of-use-Problem.

Hier ermittelst Du Werte, die du anschließend verwendest:

Die root_id ist kein Problem, denn ID-Werte ändert man ja nicht.

lft and rgt werden durch Deine Anwendung sehr wohl verändert - und genau

aus diesem Grund musst Du bereits diese Abfrage durch die Schreibsperre

absichern.

Stell Dir vor, parallel wird ein weiterer Eintrag vorgenommen, der ein

klein wenig früher an ist. Für das Eintragen wurde bereits ein WRITE

LOCK angefordert, aber die Sperre ist noch nicht in Kraft, weil folgende

Abfrage gerade noch ausgeführt wird.

$query_node_referenz="select  root_id,lft,rgt  FROM ".tableprefix."node WHERE payload ='$ReferenzID'";
$result_node_referenz=mysql_query($query_node_referenz);
if ($result_node_referenz==FALSE)
     {
          error(mysql_error(),'','',''); // ja, ich weiß, was nun kommt...
     }
$row_node_referenz=mysql_fetch_row($result_node_referenz);
$V_ROOT_ID=$row_node_referenz[0];
$V_LFT=$row_node_referenz[1];
$V_RGT=$row_node_referenz[2];

Jetzt erhält die andere Session ihr WRITE LOCK und verändert die lft und

rgt-Werte auch des Knotens, den diese Session hier bearbeitet.

// Und jetzt der neue Eintrag
$result10=mysql_query("LOCK TABLES '".tableprefix."node' WRITE");

Diese Session muss warten, bis die andere Session ihre Sperren freigegeben

hat ... und arbeitet nun mit falschen rgt- und lft-Werten in den Variablen

$V_RGT und $V_LFT.

$query_update1="UPDATE ".tableprefix."node
   SET lft      =  lft + 2
WHERE root_id  =  ".$V_ROOT_ID."
   AND lft      >  ".$V_RGT."
   AND rgt      >= ".$V_RGT."";
$result_update1=mysql_query($query_update1);

$query_update2="UPDATE ".tableprefix."node
   SET rgt      =  rgt + 2
WHERE root_id  =  ".$V_ROOT_ID."
   AND rgt      >= ".$V_RGT."";
$result_update2=mysql_query($query_update2);

$query2="
INSERT INTO ".tableprefix."node ( root_id, payload, lft, rgt )
          VALUES ( $V_ROOT_ID, $MainID, $V_RGT, $V_RGT + 1 )";
$result12=mysql_query($query2);
$result13=mysql_query("UNLOCK TABLES");

  

> Nun der Fall, dass keine ReferenzID übergeben wurde:  
  
auch dort liegt das gleiche Problem vor.  
  
  
Freundliche Grüße  
  
Vinzenz

[KlausStein]

das Ergebnis habe ich in etwa erwartet.

payload  lft    rgt
4303 1318 1319
5290 1318 1319

Hallo Vinzenz,

möglicherweise Fehlalarm, denn weißt Du, was wir vergessen haben?

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

Die Query berücksichtigt nicht, dass ich jedes Jahr neu anfange, in der node-tabelle hochzuzählen.

Verschaffe Dir einen schnellen Überblick, ob dieses Problem mehrfach vorliegt:

-- suche lft-Werte, die mehrfach auftreten - was ja ein Fehler ist.
SELECT
    COUNT(lft)
FROM
    000node
HAVING COUNT(lft) > 1

>   
  
  
Sorry, aber bei dieser Query verstehe ich einfach das Ergebnis nicht. Als Ergebnis bekomme ich die Anzahl der gesamten Einträge in der node-tabelle (2357).  
  
Aber auch diese Query berücksichtigt nicht, dass ich über die root\_id das Jahr mitführe und jährlich neu hochzähle.  
  
Insofern dürfte aber auch Deine Lösung ein paar Posts zuvor schon korrekt gewesen sein, denn das Doppelergebnis mit dem falschen Knoten wird zum Einfachergebnis mit korrektem Knoten, wenn ich eine where-Klausel mit Bezug auf die root\_id anfüge.  
  
  
Nun mal was ganz anderes. Ich mag das Nested Set nicht, weil ich die Tabelle nicht mehr vernünftig lesen kann. Dazu kommt in meinen Ausführungen das von Dir erwähnte Time-of-check-to-time-of-use-Problem. Weiterhin scheint in meiner Lösung der Jahreswechsel auch in der Ausgabe des Baumes ja nciht ganz ohne zu sein.  
  
Ich habe das gestern mal schnel, auf meine Redundanz überschrieben und stelle fest, dass ich noch nie über 6-8 Knoten für einen payload gekommen bin. Meist sind es sogar nur 1-3 Knoten (Parent und Child zusammengezählt).  
  
Und ich benötige nie den gesamten Baum, ich benötige immer nur den Parent-Cild-Baum von einem bestimmten Payload.  
  
Ist da für mich nicht sogar die alte, rekursive Ausles-Lösung in Summe (ich könnte ja die ganze Nested Set Insert und Update-Geschichte herausnehmen)gesehen performanter als das Nested Set? Und wegen o.g. Mängel auch die sinnvollere Lösung?  
  
Grüße, KlausStein