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.

> 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.
What about adding "group by a.field1,a.field2..."? It will eliminate
duplicates.
Can you send explain analyze this query? Maybe table
joins should be reordered or they need other indexes they have?


Tomasz