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