query optimization - 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.

Answer 1

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.