maria-discuss team mailing list archive
Mailing list archive
Re: why does UNION ALL use a temp table?
Hi Igor, Mark, uncle Sinisa!!1!
On 03/08/2009, at 3:03 PM, Igor Babaev wrote:
Arjen Lentz wrote:
On 01/08/2009, at 3:15 AM, MARK CALLAGHAN wrote:
Why does UNION ALL use a temp table? Many users are surprised by
It would be faster to return rows from each branch of a UNION ALL
query directly to the network.
Set a breakpoint in select_union::send_data and you'll
see how a temporary table is filled in.
Ah, found the difference... it was niggling my brain since it did
increment on the first query.
It was the query cache interfering with my test.
So indeed, Igor and Mark are correct, it's doing a tmp table... but it
really shouldn't for a UNION ALL without ORDER BY etc.... I was pretty
certain that that was the one case where it bypassed. But UNION came
in in 4.0 and so the trail of history is pretty long.... Sinisa
implemented it, I believe.
Sinisa, any ideas on this? Why does UNION ALL without ORDER BY create
a tmp table anyway?
Arjen Lentz, Director @ Open Query (http://openquery.com)
Exceptional Services for MySQL at a fixed budget.
Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org