mysql union 慢,为什么在MySQL中UNION查询这么慢？

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.

解决方案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.