Re: optimisation of outer join

On Thu, 9 Aug 2001, Nicholas Piper wrote:

> Hi everyone again ! You've been so useful that I've got more questions
> :-)
>
> Sorry about the width of my posts by the way, I'm presuming it is
> preferred to have wide tables than try and wrap them.
>
> I'm trying to select all rows from a join, and then also some other
> rows from a third join if the rows exist. If they don't, then return
> blank entries.
>
> My normal query which only returns full rows and the one I've tried to
> alter to do exactly what I want is below. The question I have is why
> my new one is so slow, and doesn't appear to be using indexes again.
>
> (The new attempt is first)
>
> depos=# explain select cdtitles.title, cdtitles.artist, song,
> fk_products_id, p.title, p.artist from cdsongs, cdtitles left join
> products p on (cdtitles.fk_products_id = p.id) where cdtitles.cdid =
> cdsongs.cdid and song like 'mushroom festi%';
> NOTICE:  QUERY PLAN:

You're forcng the cdtitles cross products join first I believe here.
I think you may want to force the other join first, so maybe...

explain select s.title, s.artist, song, fk_products_id, p.title, p.artist
from (cdsongs inner join cdtitles on (cdtitles.cdid=cdsongs.cdid)) s
 left join products p on (s.fk_products_id = p.id) where song like
 'mushroom festi%';