maria-discuss team mailing list archive

[Arjen Lentz <arjen@xxxxxxxxxxxxx>]

Hi Mark

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.


Actually, it doesn't! I just tested it with a random MySQL 5.0
If it's UNION ALL and no ORDER BY is used, no tmp table is used.
So, it acts as you wish already.

I had this in my brain already, but verified by running a SELECT *  
FROM tbl UNION ALL SELECT * FROM tbl and running SHOW SESSION STATUS  
LIKE 'created_tmp_tables' before and after. No change.

If you run EXPLAIN on it, it'll create a tmp table, but that's an  
artefact of EXPLAIN rather than the query since the query itself is  
not actually executed then.


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