On Sun, Aug 2, 2009 at 6:10 PM, Arjen Lentz<arjen@openquery.com> wrote:
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.
I cannot reproduce your results. Can you provide SQL for all of it and the version number for which this occurs? This was run for 5.0.83
drop table if exists i; create table i (i int); insert into i values (1); show session status like 'created_tmp_tables'; select * from i union all select * from i; show session status like 'created_tmp_tables';
Variable_name Value Created_tmp_tables 1 i 1 1 Variable_name Value Created_tmp_tables 3
-- Mark Callaghan mdcallag@gmail.com