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