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.
当我优化我的2个单个查询以在不到0.02秒的时间内运行然后UNION它们时，生成的查询需要超过1秒才能运行。此外，UNION ALL比UNION DISTINCT需要更长的时间。我认为允许重复会使查询运行得更快，而不是更慢。我真的最好分别运行2个查询吗？我更愿意使用UNION。
3 个解决方案
#1
15
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.
当我优化我的2个单个查询以在不到0.02秒的时间内运行然后UNION它们时，生成的查询需要超过1秒才能运行。
Do your queries include ORDER BY … LIMIT clauses?
您的查询是否包含ORDER BY ... LIMIT子句？
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.
如果在UNION之后放置ORDER BY ... LIMIT，它将应用于整个UNION，并且在这种情况下不能使用索引。
If id is a primary key, this query will be instant:
如果id是主键，则此查询将是即时的：
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 ALLtakes longer than aUNION DISTINCT. I would assume allowing duplicates would make the query run faster and not slower.此外，UNION ALL比UNION DISTINCT需要更长的时间。我认为允许重复会使查询运行得更快，而不是更慢。
This also seems to be due to ORDER BY. Sorting a smaller set is faster than a larger one.
这似乎也是由于ORDER BY。对较小的集进行排序比对较大的集更快。
Am I really just better off running the 2 queries separately? I would prefer to use the
UNION我真的最好分别运行2个查询吗？我更愿意使用UNION
Do you need the resulting set to be sorted?
您是否需要对结果集进行排序？
If not, just get rid of the final ORDER BY.
如果没有，只需摆脱最终的ORDER BY。
#2
4
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..
猜测：既然你用2个联合查询一个表，可能是，mysql很难决定表的锁定策略，或者它尝试一些缓存，这在这里不起作用，因为你查询了不相交的集合，尝试多线程访问（非常合理）但遇到一些锁定/并发/文件寻求问题..
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.
那只是我的2美分。现在不能在这里测试。
#3
0
Could it be that you measure response time and not time to retrieve all data?
可能是您测量响应时间而不是时间来检索所有数据？
#1
15
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.
当我优化我的2个单个查询以在不到0.02秒的时间内运行然后UNION它们时，生成的查询需要超过1秒才能运行。
Do your queries include ORDER BY … LIMIT clauses?
您的查询是否包含ORDER BY ... LIMIT子句？
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.
如果在UNION之后放置ORDER BY ... LIMIT，它将应用于整个UNION，并且在这种情况下不能使用索引。
If id is a primary key, this query will be instant:
如果id是主键，则此查询将是即时的：
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 ALLtakes longer than aUNION DISTINCT. I would assume allowing duplicates would make the query run faster and not slower.此外，UNION ALL比UNION DISTINCT需要更长的时间。我认为允许重复会使查询运行得更快，而不是更慢。
This also seems to be due to ORDER BY. Sorting a smaller set is faster than a larger one.
这似乎也是由于ORDER BY。对较小的集进行排序比对较大的集更快。
Am I really just better off running the 2 queries separately? I would prefer to use the
UNION我真的最好分别运行2个查询吗？我更愿意使用UNION
Do you need the resulting set to be sorted?
您是否需要对结果集进行排序？
If not, just get rid of the final ORDER BY.
如果没有，只需摆脱最终的ORDER BY。
#2
4
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..
猜测：既然你用2个联合查询一个表，可能是，mysql很难决定表的锁定策略，或者它尝试一些缓存，这在这里不起作用，因为你查询了不相交的集合，尝试多线程访问（非常合理）但遇到一些锁定/并发/文件寻求问题..
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.
那只是我的2美分。现在不能在这里测试。
#3
0
Could it be that you measure response time and not time to retrieve all data?
可能是您测量响应时间而不是时间来检索所有数据？