Re: sub-query optimization

Brad Hilton wrote:
<cut>
> select * from articles where exists
> (select 1 from article_categories, categories, category_map
> where
> article_categories.article_id = articles.id and
> categories.restrict_views = FALSE and
> article_categories.category_id = categories.id and
> category_map.parent_id = 1 and
> category_map.child_id = categories.id and
> category_map.child_id = article_categories.category_id and
> articles.post_status = 'publish'
> )
> and
> post_status = 'publish'

According to your table definition I can say, that you don't need subselect
and exists, because 1 row from article and 1 row from categories have only 1
hit row in articles_categories (primary key), so you can rewrite your query
as simple joins:
(Query is only a hint, it probably won't work)

select a.*
from
categories_c cross join category_map m
join articles a on (child_id=category_id)
join articles_categories ac using (article_id,category_id)
where
m.parent_id=1 and not c.restrict_views;
and a.post_status='publish'

You can change join order depending on your table stats.

Regards,
Tomasz Myrta