Re: Optimising Union Query.

In article <4268F322.1040106@thales-is.com>,
Rob Kirkbride <rob.kirkbride@thales-is.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@interlog.com