maria-discuss team mailing list archive

[Arjen Lentz <arjen@xxxxxxxxxxxxx>]

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?
Thanks

Cheers,
Arjen.
--
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