Re: Nested Sets vs. Nested Intervals

asdf wrote:
> Thank you very much!
>
> You said the examples are fast, is there any significant speed
> difference to the nested intervals model?
> What's the speed difference between the multiple selects adjacency list
> example shown above and this:
>
> SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as
> lev4 t5.name as lev5 t6.name as lev6
> FROM web_directory AS t1
> LEFT JOIN web_directory AS t2 ON t2.parent = t1.id
> LEFT JOIN web_directory AS t3 ON t3.parent = t2.id
> LEFT JOIN web_directory AS t4 ON t4.parent = t3.id
> LEFT JOIN web_directory AS t5 ON t5.parent = t4.id
> LEFT JOIN web_directory AS t6 ON t6.parent = t5.id
> WHERE t1.name = 'Games'
> AND t2.name = 'Board Games'
> AND t3.name = 'Abstract';
> AND t4.name = 'Chess';
> AND t5.name = 'Software';
> AND t6.name = 'Web Chess Viewers';

Why it's outer join? I assume you've meant normal join.

This query should be quick as well, assuming that optimizer finds the correct plan. I can imagine many nodes with the names 'Abstract' and 'Software' in different places of the hierarchy. Being realistic, however, the optimizer would never start from those tables, which are happen to be inner nodes in the join graph. The most likely join order alternatives are t1->t2->t3->t4->t5->t6, which is as efficient as your earlier version, and
t6->t5->t4->t3->t2->t1 which is also efficient. Well, you'd better have compound index on (parent, name).

> What speed advantages do the nested intervals model have that that
> adjacency list doesn't?

Given node id find the node level. Received on Thu Nov 17 2005 - 02:12:23 CET