带有LIMIT的SQL UNION是否优化了不需要的查询？

Say I have a sequence of queries that progressively get more expensive. I can join all these with a union but I'm only interested in the top 10 results so I add a LIMIT. The query looks something like this:

假设我有一系列逐渐变得更加昂贵的查询。我可以加入所有这些联盟，但我只对前10名结果感兴趣，所以我添加了一个LIMIT。查询看起来像这样：

(SELECT col1, col2 FROM table WHERE colx = 'x')
UNION
(SELECT col1, col2 FROM table WHERE colx LIKE '%x%')
UNION
(SELECT col1, col2 FROM table WHERE colx LIKE '%x%' AND unindexedcol LIKE '%y%')
LIMIT 10

I know that this is not guaranteed to be the top 10 from the first query as the MySQL documentation states

我知道这不能保证是MySQL文档声明的第一个查询的前10名

UNION by default produces an unordered set of rows

UNION默认生成一组无序行

but in practice it appears that the results are ordered select 1 followed by select 2 etc until the limit is reached.

但在实践中，似乎结果是按顺序选择1，然后选择2等，直到达到限制。

So does MySQL execute the queries sequentially and stop when it hits the limit? If not then what is a better way to execute this style of query where only a subset of results are needed and prioritisation of less expensive queries is preferred to minimize execution time?

那么MySQL是否按顺序执行查询并在达到限制时停止？如果没有那么什么是更好的方式来执行这种查询样式，其中只需要一部分结果，并且优先考虑较便宜的查询以最小化执行时间？

2 个解决方案

#1

I have made a test to demonstrate MySQL does not optimize that kind of query: First of all I execute this query:

我做了一个测试来证明MySQL没有优化那种查询：首先我执行这个查询：

select @a1 := 0;
select @a2 := 0;

(SELECT id, @a1:=@a1+1 FROM companies)
UNION
(SELECT id, @a2:=@a2+1 FROM invoices)
LIMIT 10;

Then I watch the values of @a1 and @a2 variables:

然后我观察@ a1和@ a2变量的值：

select @a1 as a1, @a2 as a2;

In my tests they are a1 = 81 and a2 = 467. So the single selects of union query are not limited.

在我的测试中，它们是a1 = 81和a2 = 467.因此联合查询的单个选择不受限制。

#2

Since you want only 10 rows from union, you can add LIMIT 10 to each of subqueries, so that each of them returns at most 10 rows.

由于你只需要联合10行，你可以为每个子查询添加LIMIT 10，这样每个子查询最多返回10行。

#1