Re: Nested Sets vs. Nested Intervals

asdf wrote:
> The rating is between 0 and 100.
>
> Is this more efficient to select the top ten listings:
>
> SELECT listings.*
> FROM listings
> WHERE listings.lft_index
> BETWEEN node.a11/node.a21 - 0.00000001 AND
> (node.a11-node.a12)/(node.a21-node.a22) + 0.00000001
> AND listings.rating >= 50
> ORDER BY listings.rating DESC LIMIT 0, 10
>
> or this:
>
> DECLARE cnt INT;
> DECLARE number INT;
>
> SET number = 75;
>
> REPEAT
>
> SET number = number - 3;
>
> SELECT COUNT(*) INTO cnt
> FROM listings
> WHERE listings.lft_index
> BETWEEN node.a11/node.a21 - 0.00000001 AND
> (node.a11-node.a12)/(node.a21-node.a22) + 0.00000001
> AND listings.rating >= number
>
> UNTIL cnt >= 10
> END REPEAT;
>
> SELECT listings.*
> FROM listings
> WHERE listings.lft_index
> BETWEEN node.a11/node.a21 - 0.00000001 AND
> (node.a11-node.a12)/(node.a21-node.a22) + 0.00000001
> AND listings.rating >= number
> ORDER BY listings.rating DESC LIMIT 0, 10

The second solution looks more inefficient than the first one. You simply do the same work many times.

If I understand your idea correctly, you can create composite index on (rating,lft). But then the only way to leverage this index is when you have equality predicate on rating. So you can do iterations like you do: start with rating 100, and decrement rating by one in a loop.

Suppose, there are 10 sites rated 100 under some category, that we query. Then they would be found immediately on the first iteration. What if there is no high rated sites below some category? Then you'll have to do many iterations, and that might be slower that the other method. Received on Mon Dec 05 2005 - 04:08:33 CET