Performance problems with view and union

[Niklas Johansson]

Hi,
I'm having performance problem with a view using a union. I'm running MySQL 5.0.7 on Windows XP.

My view looks something like this

create view troubleview as
select id, col1, col2 from hugetable1
union
select id, col1, col2 from hugetable2

The two selects in the view are a bit more complex using joins on a couple of table, but I think you get the idea. The id field is the primary key of the tables.

When i run a query query like "select * from troubleview where id='somevalue'" it takes a very long time to complete. In fact I have never waited for it to complete, after half an hour I killed the MySQL process.

When I run the query on the subqueries of the union once at a time I get the response very fast.

It seems to me that MySQL needs to actually complete the queries of the union before it aplies my id='somevalue' condition. Is that really the case? If so in my case the union will result in a couple of hundred millions rows just to return the two matching my id='somevalue' condition. Shouldn't my condition be aplied to both of the subqueries of the union?

Any help is appreciated.

BR Niklas