Florian Weimer wrote:
>* Kyle Bateman:
>
>
>
>>Any ideas about whether/how this can be done?
>>
>>
>
>If the project tree is fairly consistent, it's convenient to encode it
>using intervals instead of parent/child intervals. IIRC, Celko's "SQL
>for smarties" explains how to do this, and Kristian Koehntopp has
>written some PHP code to implement it.
>
>
>
I agree that this produces a more efficient query for finding the
projects that are the progeny of another project, but I'm trying to
figure out how that helps me select the right project transactions from
my ledger efficiently.
This query produces wonderful results (very fast):
select * from ledger where proj >= 4737 and proj <= 4740;
But I'm assuming that using an interval-encoded project tree, I would
have to do something like the following to get a progency group:
select * from ledger l, proj p where p.proj_id = l.proj and p.left >
1234 and p.right < 2345;
The problem (at least according to my initial testing) is that this
forces a join of the entire ledger and I get my slowest performance
group (5 seconds).
What am I missing?
Kyle