Does a SQL UNION with a LIMIT optimize away uneeded queries?

I have made a test to demonstrate MySQL does not optimize that kind of query: First of all I execute this query: select @a1 := 0; select @a2 := 0; (SELECT id, @a1:=@a1+1 FROM companies) UNION (SELECT id, @a2:=@a2+1 FROM invoices) LIMIT 10 Then I watch the values of @a1 and @a2 variables: select @a1 as a1, @a2 as a2 In my tests they are a1 = 81 and a2 = 467. So the single selects of union query are not limited.

I have made a test to demonstrate MySQL does not optimize that kind of query: First of all I execute this query: select @a1 := 0; select @a2 := 0; (SELECT id, @a1:=@a1+1 FROM companies) UNION (SELECT id, @a2:=@a2+1 FROM invoices) LIMIT 10; Then I watch the values of @a1 and @a2 variables: select @a1 as a1, @a2 as a2; In my tests they are a1 = 81 and a2 = 467. So the single selects of union query are not limited.

Thanks. Great way to illustrate what's going on behind the scenes. Now I need to find a way to eliminate the expensive queries if not required – Dolbz Feb 11 at 11:27.

Since you want only 10 rows from union, you can add LIMIT 10 to each of subqueries, so that each of them returns at most 10 rows.

2 Mchl, That's indeed a good piece of advice but it doesn't answer the question. Are the most expensive queries (being the last the most expensive) ruled out if the first one (or the second one) reaches the limit? – andre matos Feb 11 at 10:09 1 I'm pretty sure they aren't.

– Mchl Feb 11 at 10:11 Thanks for clarifying! However Lorenzo has different opinion, which makes your advice to limit the subqueries even more valuable! :-) – andre matos Feb 11 at 10:21 I believe that the advice to limit every select at 10, and then the global one at 10 again it's a very good advice.

I would do the same :) – LorDalCol Feb 11 at 10:32 Perhaps I was not clear. My opinion was same as Lorenzo's demonstration has shown. – Mchl Feb 11 at 11:05.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.