Re: optimisation of outer join

On Thu, 09 Aug 2001, Stephan Szabo wrote:

> > 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%';

Fantastic, that works great ! Thanks :-)

Nick

--
Part 3 MEng Cybernetics; Reading, UK http://www.nickpiper.co.uk/
Change PGP actions of mailer or fetch key see website 1024D/3ED8B27F
Choose life. Be Vegan :-) Please reduce needless cruelty + suffering !