1) If the join to rank_lookup is done across Pkey and/or unique indexed fields and a foreign key is defined for said join, I don't know how the subquery could possibly be more optimized then it is (the reasoning being beyond the scope of this discussion and possibly even beyond the scope of area in which I can safely comment :)
2) It is my understanding and experience (I could be unaware of a trick or special case specifics, however) that using an IN clause is LESS efficient then joining to the table. The only reason I used the in clause is because, as you indicated, you were only ask for the top five, which is a very small set (you probably would not want to do that if the set was large).
Indeed, the IN clause is a de-optimization, it only HAPPENS to make the query run faster because it allows you to avoid calling the select function for all but the selected 5 rows (which was the goal you requested)
3) In SQL there is almost always more then 1 way of doing something, you have now seen 2. There may be more, possibly even better ways. Experts care to comment? :)
Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085
Markus Schiltknecht wrote:
Hello Terry,
Thanks a lot. That's so simple I didn't see it. (The original query is
much more complex.)
The only problem is, rank is not a column of category itself, but a
joined row. With this solution, the join will have to be performed
twice. But since this doesn't cost that much and because the second join
is only done for 5 rows at the max this does not hurt.
The more complete query now looks a little ugly:
SELECT id, get_category_text_path(id), r.rank
FROM category
JOIN rank_lookup AS r ON cat_id = id
WHERE id IN ( SELECT c.id FROM category AS c JOIN rank_lookup AS rr ON rr.cat_id = c.id ORDER BY rr.rank LIMIT 5
)
It's not possible to optimize out that second join, is it?
Regards
Markus
On Tue, 2006-05-02 at 07:39 -0400, Terry Fielder wrote:
SELECT id, get_category_text_path(id)
FROM category
WHERE id IN ( SELECT c.id FROM category AS c ORDER BY c.rank LIMIT 5
)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
