← Back to team overview

maria-discuss team mailing list archive

Re: why does UNION ALL use a temp table?

 

Hi Mark

On 03/08/2009, at 1:35 PM, MARK CALLAGHAN wrote:
On Sun, Aug 2, 2009 at 6:10 PM, Arjen Lentz<arjen@xxxxxxxxxxxxx> 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.


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



Ye sorry I need to clarify more...

At first i actually ran it on an "old" 5.1.30, with the results as described. I repeated it on 5.0.77-ourdelta just now. However, 5.0 will increment created_tmp_tables for that SHOW command, whereas 5.1.30 does not. You can verify this by just running the SHOW twice in a row and seeing it increment just by itself. The SELECT does not increment it by more. For added fun, the first time you run through the sequence (same as yours) it does increment by 2 for reasons I can't think of right now. But if you do it again, you'll see it only increments by one just for the SHOW.

Verify this behaviour by running
- SHOW a few times in a row without the SELECT (should increment by 1 for the SHOW command), and also - SHOW-SELECT-SELECT-SELECT-SHOW (which should still increment only by 1, not 3+1).

Does that jive for you?


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




References