Bug #50674 Do not create temporary tables for UNION ALL
Submitted: 27 Jan 2010 19:52 Modified: 14 Nov 2013 16:49
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0,5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALL, table, temporary, UNION

[27 Jan 2010 19:52] Mark Callaghan
Description:
mysqld uses a temp table for both UNION and UNION ALL processing. The temp table for UNION ALL is not needed as results can be immediately returned to the client. That change would save the cost of writing the temp table and possibly spilling it to disk. It would also return the first-N rows to the client much faster.

How to repeat:
na

Suggested fix:
na
[28 Jan 2010 5:00] Valeriy Kravchuk
Thank you for the feature request.
[16 Feb 2010 16:07] MySQL Verification Team
There are two problems regarding this feature request.

First problem, which is much smaller and that could be resolved easily, is checking out all UNION's in the query, while taking cares of those that are bracketed, id est in braces.

But, the major problem are typeholders. Currently, automatic conversions of values from one type to another is due through MySQL server classes of fields. If you are inserting a value that does not belong fully to the type, like INT to VARCHAR, the only mechanism at disposal for this automatic conversion is the usage of tables. Hence, that is why insertion into temporary tables solves this big problem of value conversion. The subject here is, of course, different values coming to the fields in the same order from subsequent SELECT nodes in the UNION. This is doable too, but it would entail writing of a large amount of new code.
[15 Jul 2010 11:35] Domas Mituzas
Sinisa, MySQL has to know already some types when it creates temporary table, right? Why can't it just use those types for sending data out to clients?
[15 Jul 2010 12:48] Mark Callaghan
Apparently some people don't enjoy watching a server run out of swap or temp disk space because of the unnecessary use of temp tables. This is probably more of a problem for people who support MySQL in ops as they are the ones who must respond.

Can we change the severity/status of this by mentioning that this is a great way to take out a mysqld server?
[15 Jul 2010 13:31] MySQL Verification Team
if number of unions is big enough, bug #44626 makes situation worse.
[24 Feb 2011 15:50] Mark Jeffrey
I am surprised to see that this is not regarded as an important issue, we just ran up against it while using hibernate. 
Hibernate uses union all when doing polymorphic queries in the TABLE_PER_CLASS strategy and due to this CRITICAL bug the server quickly dies with large volumes of data.
[21 Oct 2011 6:46] laur kanzer
This could affect the following code now no other way to do it better

CREATE TEMPORARY TABLE final 
SELECT `content_field_l1_lista`.`field_l1_lista_value` 
FROM content_field_l1_lista 
UNION ALL 
SELECT `content_field_l2_lista`.`field_l2_lista_value` 
FROM content_field_l2_lista 
UNION ALL 
SELECT `content_field_l3_lista`.`field_l3_lista_value` 
FROM content_field_l3_lista; 
 
SELECT COUNT( * ) , `final`.`field_l1_lista_value` 
FROM final 
GROUP BY `final`.`field_l1_lista_value` 
HAVING COUNT( * ) 
ORDER BY COUNT( * ) DESC 
LIMIT 0 , 30;
[14 Nov 2013 16:49] Paul DuBois
Noted in 5.7.3 changelog.

The server no longer uses a temporary table for UNION statements that
meet certain qualifications. Instead, it retains from temporary table
creation only the data structures necessary to perform result column
typecasting. The table is not fully instantiated and no rows are
written to or read from it; rows are sent directly to the client. As 
a result, The result is reduced memory and disk requirements, and
smaller delay before the first row is sent to the client because the
server need not wait until the last query block is executed. EXPLAIN
and optimizer trace output will change: The UNION RESULT query block
will not be present because that block is the part that reads from
the temporary table.

The conditions that qualify a UNION for evaluation without a
temporary table are:

* The union is UNION ALL, not UNION or UNION DISTINCT.

* There is no global ORDER BY clause.

* The union is not the top-level query block of an {INSERT | REPLACE}
  ... SELECT ... statement.