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.