Re: Optimising Union Query.

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);

this query might be helped by an index on location in each of those
three tables, but probably not.
--

Patrick TJ McPhee
North York Canada
ptjm(at)interlog(dot)com