Re: Optimising Union Query.

On 4/22/05, Rob Kirkbride <rob(dot)kirkbride(at)thales-is(dot)com> wrote:
> I've got a query that takes quite some time to complete. I'm not an SQL
> expert so I'm not sure how to improve things.
> 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
> do it much quicker by a use of an index or something. Each of the three
> tables could have several thousand entries in. Basically the tables
> contain data recorded against time then every hour a script deletes
> entries that more than so many hours old.
>
> 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;

Would it be OK if there were duplicates returned? I.e if select from pa
table and andu table returned same row, would it be ok if there would
be two rows in "final" output because of one?

If so, change "union" to "UNION ALL".

If you put only "UNION", server gets resutls from _all_ selects, removes
duplicates and returns your query. If you put "UNION ALL" it simply
does three selects and returns all the rows returned. Not having to
look for duplicates makes it WAY faster.

Regards,
Dawid