Re: sub-query optimization

On Fri, Feb 14, 2003 at 02:39:31PM -0800, Brad Hilton wrote:
> If I just utilize article_categories primary key, I could end up with
> duplicate articles since articles can live in multiple categories.
You can use group by to eliminate duplicates.

> In case I'm not understanding your suggestiong perfectly, I tried to
> flesh it out a bit more.  Does the following query match your
> suggestion?
It looks ok now. Probably it needs some cosmetics changes.
>
> select a.*
> from
>  categories c cross join category_map m
>  join article_categories ac on (c.id = ac.category_id and m.child_id =
> ac.category_id)
>  join articles a on (a.id = ac.article_id)
> where
>  m.parent_id=1 and
>  not c.restrict_views and
>  m.child_id = c.id and
>  a.post_status='publish'
>
> Unfortunately, this query returns duplicate articles (see explanation
> above), and is fairly slow.  Maybe I didn't follow your initial query
> properly.
Can you send explain analyze this query? Maybe table
joins should be reordered or they need other indexes they have?

Tomasz