← Back to team overview

maria-discuss team 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 this.
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