Re: Optimising Union Query.

On Sat, Apr 23, 2005 at 10:39:14PM +0000, Patrick TJ McPhee wrote:
> In article <4268F322(dot)1040106(at)thales-is(dot)com>,
> Rob Kirkbride <rob(dot)kirkbride(at)thales-is(dot)com> wrote:
>
> % I've done a explain analyze and as I expected the database has to check
> % every row in each of the three tables below but I'm wondering if I can
>
> This is because you're returning a row for every row in the three
> tables.
>
> % select l.name,l.id from pa i,locations l where i.location=l.id union
> % select l.name,l.id from andu i,locations l where i.location=l.id union
> % select l.name,l.id from idu i,locations l where i.location=l.id;
>
> You might get some improvement from
>
> select name,id from locations
> where id in (select distinct location from pa union
> select distinct location from andu union
> select distinct location from idu);

Note that SELECT DISTINCT is redundant with a plain UNION. By
definition, UNION does a DISTINCT. In fact, this is going to hurt you;
you'll end up doing 4 distinct operations (one for each SELECT DISTINCT
and one for the overall UNION). Unless some of those tables have a lot
of duplicated location values, you should either use UNION ALLs or drop
the DISTINCTs. Note that going with DISTINCTs is different than what
your original query does.

You should also consider this:

SELECT name, id FROM locations l
WHERE EXISTS (SELECT * FROM pa p WHERE p.location=l.id)
OR EXISTS (SELECT * FROM andu a WHERE a.location=l.id)
OR EXISTS (SELECT * FROM idu i WHERE i.location=l.id)

This query would definately be helped by having indexes on
(pa|andu|idu).location.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"