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: | |
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
[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.