maria-discuss team mailing list archive

[MARK CALLAGHAN <mdcallag@xxxxxxxxx>]

On Sun, Aug 2, 2009 at 6:10 PM, Arjen Lentz<arjen@xxxxxxxxxxxxx> 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@xxxxxxxxx