[MySQL] COUNT resultaat in WHERE? (Celko Tree)

[lucasvanlierop]

Had bij mijn vorige post per ongeluk al op enter gedrukt en toen ik het gewijzigde bericht wilde posten was de topic al gesloten.

Bij deze nogmaals mijn probleem:

Ik ben bezig met het zo optimaal mogelijk uitlezen van een boom structuur uit een mysql tabel.

Met behulp van de volgende query

code:

SELECT
N1.node_id
,N1.node_properties_id
,N1.node_name
,N1.node_title
,N1.node_type
,COUNT(N2.node_id) AS node_level
FROM nodes AS N1
JOIN nodes AS N2
ON N1.node_lft BETWEEN N2.node_lft AND N2.node_rgt
JOIN nodes AS N3
ON N3.node_id = '64'
WHERE
N1.node_lft BETWEEN (N3.node_lft + 1) AND (N3.node_rgt - 1)
GROUP BY N1.node_id ORDER BY N1.node_lft

Het resultaat van deze query is een lijst met alle 'nodes' vanaf een bepaalde node (hier bv met id nr 64) met daarbij het niveau als 'node_level' (het resultaat van de COUNT())

Wat ik nu doe is in PHP filteren op een bepaald node_level dat is natuurlijk ranzig, maar de andere optie is het gebruiken van een verschrikkelijk trage JOIN Query.

Mijn vraag is dus is het op een of andere manier mogelijk om de waarde van COUNT() te gebruiken in de WHERE clause? dat zou nl hetzelfde resultaat op kunnen leveren lijkt me.

[cameodski]

Ik denk dat je op zoek bent naar HAVING. Bijv:

code:

GROUP BY ...
HAVING (COUNT (*) = 2)

[ Voor 45% gewijzigd door cameodski op 28-04-2004 16:25 ]

[lucasvanlierop]

Yeah :-)

Ik was HAVING wel tegengekomen maar had nog niet het licht gezien om het zo
toe te passen, volgens mij werkt het perfect zo!

Mijn dank is groot, nu maar gelijk even melden op het forum waar ik de trage query (van Joe Celko himself) ooit vond.

Mijn dank is groot.

[ACM]

Als je het nog efficienter wilt uit kunnen lezen, zou je eventueel nog de level op kunnen nemen als "cached" waarde in je tabel.
Die waarde moet dan natuurlijk 1 groter dan de level van de parent zijn en blijven, maar het schuiven en knoeien in een nested-set is sowieso rotwerk, dus die extra stap om die waarde te cachen (en dus een group by/having over te kunnen slaan bij elke select!) is denk ik niet al te veel

Een andere optimalisatie kan het verschuiven van je join's naar de where-part zijn, zodat MySQL eventueel efficienter de index kan toepassen.

[ Voor 18% gewijzigd door ACM op 28-04-2004 17:04 ]

[cameodski]

ACM schreef op 28 april 2004 @ 17:02:
Een andere optimalisatie kan het verschuiven van je join's naar de where-part zijn, zodat MySQL eventueel efficienter de index kan toepassen.

Is de query optimizer van MySQL niet zo intelligent dat ie beide varianten even efficiënt uitvoert?

[P_de_B]

ACM schreef op 28 april 2004 @ 17:02:
Als je het nog efficienter wilt uit kunnen lezen, zou je eventueel nog de level op kunnen nemen als "cached" waarde in je tabel.
Die waarde moet dan natuurlijk 1 groter dan de level van de parent zijn en blijven, maar het schuiven en knoeien in een nested-set is sowieso rotwerk, dus die extra stap om die waarde te cachen (en dus een group by/having over te kunnen slaan bij elke select!) is denk ik niet al te veel

Een andere optimalisatie kan het verschuiven van je join's naar de where-part zijn, zodat MySQL eventueel efficienter de index kan toepassen.

Ik zou verwachten dat dat door de query engine wel gedaan wordt?

_{spuit 11}

[ Voor 4% gewijzigd door P_de_B op 28-04-2004 17:08 ]

[lucasvanlierop]

Ik zal de effiency eens testen met query time en EXPLAIN, kijken of dat verschil oplevert.

en misschien is het cachen van het level ook wel een idee, dat verandert inderdaad niet zovaak.

Verschuiven etc is opzich redelijk hell in nested sets maar als je er eenmaal uit hoe het moet bent doet het scriptje de rest, zit me wel af te vragen of stored procedures straks in MYSQL 5 daar iets in kunnen betekenen (ben daar nog niet in thuis)

[ACM]

cameodski schreef op 28 april 2004 @ 17:07:
Is de query optimizer van MySQL niet zo intelligent dat ie beide varianten even efficiënt uitvoert?

Ik heb absoluut geen hoge pet van MySQL's optimizer, het kan best dat ie ze als gelijkwaardig beschouwt, maar zeker bij MySQL kan het geen kwaad even te kijken of dat ook het geval is.

Flakkerbei schreef op 28 april 2004 @ 17:25:
Verschuiven etc is opzich redelijk hell in nested sets maar als je er eenmaal uit hoe het moet bent doet het scriptje de rest, zit me wel af te vragen of stored procedures straks in MYSQL 5 daar iets in kunnen betekenen (ben daar nog niet in thuis)

't Grootste nadeel zijn de inserts en verschuivingen, de verschuivingen zal je relatief weinig doen dus blijven de inserts over.
Celko is een boek aan het uitgeven over bomen: "Joe Celko's SQL for Smarties : Trees and Hierarchies". Daar worden als het goed is allerlei uitwerkingen, voor- en nadelen in besproken van o.a. de nested set en andere boomoplossingen.

[lucasvanlierop]

Ja ik kom dat boek overal tegen inderdaad dus dat ga ik maar eens bestellen.

Ik moet zeggen dat het (snel) uitlezen nog de grootste uitdaging is al is geen recursie en alles met 1 query wel heel fijn!

Verschuiven (binnen het zelfde niveau) gebeurt nog wel eens in mijn applicatie

Uit de boomstructuur worden o.a. ook uitklapmenu's genenereerd en daarvoor maak ik dankbaar gebruik van de volgorde die vastligt in de boom, menutje omhoog schuiven is eenvoudig door de boom even aan te passen (functietje voor gemaakt)
de gebruiker (contentbeheerder) ziet alleen pijltje omhoog/laag en weet niet beter dan dat het heel makkelijk is.

Die Celko is wel slimme kerel!