Why are UNION queries so slow in MySQL?

Question

When I optimize my 2 single queries to run in less than 0.02 seconds and then UNION them the resulting query takes over 1 second to run. Also, a UNION ALL takes longer than a UNION DISTINCT.

I would assume allowing duplicates would make the query run faster and not slower. Am I really just better off running the 2 queries separately? I would prefer to use the UNION.

As a simple example if I do

SELECT name FROM t1 WHERE field1 = true

it takes .001 seconds

and if I do

SELECT name FROM t1 WHERE field1 = false

it takes .1 seconds.

If I then run

SELECT name FROM t1 WHERE field1 = true 
UNION ALL 
SELECT name FROM t1 WHERE field1 = false

it takes over 1 second.

Unfortunately I cannot (work), it is a pretty straight forward query though I think. Perhaps I need some insight on how UNIONS are performed by MySQL. I notice on an explain for the joined query I am using filesort and temp table whereas each individual query is not. — Greg, May 15 '09 at 18:13
As a simple example if I do SELECT name FROM t1 WHERE field1 = true it takes .001 and if I do SELECT name FROM t1 WHERE field1 = false it takes .1 seconds. If I then run SELECT name FROM t1 WHERE field1 = true UNION ALL SELECT name FROM t1 WHERE field1 = false it takes over 1 second. It is not the result of a specific query. — Greg, May 15 '09 at 18:20
i am not sure why this 10 y.o. question has a bounty. MySQL does not have this issue from what i tested: http://sqlfiddle.com/#!9/4668601/2 — smoore4, Dec 02 '19 at 18:56
Perhaps some of us are expecting each SELECT to be queried instantaneously and then merge-sorted directly to the client. — William Entriken, Feb 03 '20 at 19:10

Quassnoi · Answer 1 · 2009-05-16T07:20:44.237

18

When I optimize my 2 single queries to run in less than 0.02 seconds and then UNION them the resulting query takes over 1 second to run.

Do your queries include ORDER BY … LIMIT clauses?

If you put an ORDER BY … LIMIT after a UNION, it gets applied to the whole UNION, and indexes cannot be used in this case.

If id is a primary key, this query will be instant:

SELECT  *
FROM    table
ORDER BY id
LIMIT 1

, but this one will not:

SELECT  *
FROM    table
UNION ALL
SELECT  *
FROM    table
ORDER BY id
LIMIT 1

Also, a UNION ALL takes longer than a UNION DISTINCT. I would assume allowing duplicates would make the query run faster and not slower.

This also seems to be due to ORDER BY. Sorting a smaller set is faster than a larger one.

Am I really just better off running the 2 queries separately? I would prefer to use the UNION

Do you need the resulting set to be sorted?

If not, just get rid of the final ORDER BY.

edited May 16 '09 at 07:20

answered May 16 '09 at 00:36

Quassnoi

413,100
91
616
614

what if i need the `order by` removing it. makes my query 2.5secs, with it can go up to 45secs. – shorif2000 Jun 11 '13 at 10:14
@sharif: what kind of an answer do you expect? – Quassnoi Jun 11 '13 at 13:28
No, you have to add parens to get the `ORDER BY` and `LIMIT` to apply to the `UNION`: `(SELECT ...) UNION (SELECT ...) ORDER BY .. LIMIT..` – Rick James Nov 25 '19 at 23:01

Rick James · Answer 2 · 2019-11-26T19:42:45.570

Concerning UNION

UNION DISTINCT (the default for UNION) is necessarily slower because it must collect the two results, then dedup. However, since there is less being returned to the client, there could some compensation there.
Until recent versions, all UNIONs involved a temp table to collect the results, hence UNION was necessarily slower than two separate SELECTs. Recently, (MySQL 5.7, MariaDB 10.1) some cases of UNION ALLwere improved to deliver the data from one SELECT straight to the client, then deliver the others.
SELECT .. UNION SELECT .. ORDER BY .. is equivalent to
(SELECT .. UNION SELECT ..) ORDER BY .. -- this
(SELECT ..) UNION (SELECT .. ORDER BY ..) -- not this
Suggest always using parens around each SELECT.
Sorting (via ORDER BY) may take extra time for whatever (select or union) it is attached to. It is very unlikely to take less time. Simply put, the Optimizer aims to do whatever is fastest, which might happen to be sorted.
All these statements apply to InnoDB; MyISAM, which is not supported much, may be missing some recent optimizations.
Sometimes a single SELECT with an OR can be sped up by turning it into a UNION, thereby letting two indexes be used.

Concerning testing:

A time of 0.001 smells like you have run the query before and the result is cached in the "Query cache". Avoid that by turning off the QC or adding SQL_NO_CACHE.
Selecting WHERE flag = true (or false) has several cases: Is flag indexed? Is flag almost always one of those values? In that case, the index will be used, and probably faster than the other case.
If you disagree with any of my statements, please provide a working example to the contrary.

Your statement about UNION .. ORDER BY doesn't seem to be correct. See: [docs](https://dev.mysql.com/doc/refman/8.0/en/union.html) "*To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT*" and [this fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9d8fe92b87dcfb7d30f69f105cb5c8b4). So the first query is equivalent to the third one. But maybe I'm just reading your answer wrong :-) — Paul Spiegel, Nov 26 '19 at 18:04

score 5 · Answer 3 · answered May 15 '09 at 18:48

A guess: Since you query one table with 2 unions, it might be,that mysql has difficulties to decide on a locking strategy for the table, or it tries some caching, that doesn't work here since you query for disjoint sets, tries to multithread the access (very reasonable) but runs into some locking/concurrency/file-seeking issues..

unions might also generally employ a higher safety setting, since these two selects have to be consistent. If you put them into separate transactions, they do not.

Experiment: Make a duplicate of the table and union those. If I'm right, it should be faster.

Possible solution: Split the single file into multiple files, to allow for better concurrency strategies. This wouldn't/shouldn't help with locking issues, but rules out the multithreading/seeking problems in the database.

It would be useful to know, which storage engine you use.

Well just my 2 cents. Can't test this here right now.

im using Innodb, and MyIsam – shorif2000 Jun 12 '13 at 10:14 — shorif2000, Jun 12 '13 at 10:14

score 1 · Answer 4 · answered Dec 02 '19 at 11:08

UNION ALL is faster than UNION because plain UNION is expecting that within two joined datasets are duplicates which need to be removed. If you can ensure (by inner WHERE clauses) that there will be no duplicates, it's far better to use UNION ALL and let database engine optimize the inner selects.

Using a WHERE clause on the result of grouped results is too expensive because you are operating on more internal results than you need. Also, the database engine's optimization can’t be processed — the results don't have anything in common.

check this link for details https://dzone.com/articles/performance-tip-for-tuning-sql-with-union

score 0 · Answer 5 · answered Sep 16 '09 at 10:08

0

Could it be that you measure response time and not time to retrieve all data?

answered Sep 16 '09 at 10:08

erikkallen

33,800
13
85
120

Why are UNION queries so slow in MySQL?

5 Answers5

Linked