[Maria-discuss] why does UNION ALL use a temp table?
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.
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.
Mark,
Yes, it would be faster. Yet, I'm afraid that changing the current implementation of UNION ALL is not a matter of a hundred of lines. Of course if somebody could find a sponsor for this optimization we would resolve the problem much sooner.
Regards, Igor.
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.
On Sun, Aug 2, 2009 at 6:10 PM, Arjen Lentzarjen@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
Hi Mark
On 03/08/2009, at 1:35 PM, MARK CALLAGHAN wrote:
On Sun, Aug 2, 2009 at 6:10 PM, Arjen Lentzarjen@openquery.com 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 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.
Cheers, Arjen.
Arjen,
Set a breakpoint in select_union::send_data and you'll see how a temporary table is filled in.
Regards, Igor.
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.
participants (3)
-
Arjen Lentz -
Igor Babaev -
MARK CALLAGHAN