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