我可以强制mysql首先执行子查询吗？

I have query like this:

我有这样的查询:

SELECT `table_1`.* from `table_1`
  INNER JOIN `table_2` [...]
  INNER JOIN `table_3` [...]
WHERE `table_1`.`id` IN(
  SELECT `id` FROM [...]
)
AND [more conditions]

When I use EXPLAIN, there is 'DEPENDENT SUBQUERY' at the end, but I want this subquery to be performed first, before other conditions.

当我使用EXPLAIN时,最后会有'DEPENDENT SUBQUERY',但我希望在其他条件之前先执行此子查询。

Is is possible?

有可能吗?

5 个解决方案

#1

SELECT  `table_1`.*
FROM    `table_1`
INNER JOIN
        `table_2` [...]
INNER JOIN
        `table_3` [...]
WHERE   `table_1`.`id` IN
        (
        SELECT  `id`
        FROM    [...]
        )
        AND [more conditions]

If the inner table is properly indexed, the subquery here is not being "performed" at all in a strict sense of word.

如果内部表被正确编入索引,那么这里的子查询根本不是严格意义上的“执行”。

Since the subquery is a part of an IN expression, the condition is pushed into the subquery and it's transformed into an EXISTS.

由于子查询是IN表达式的一部分,因此将条件推送到子查询中并将其转换为EXISTS。

In fact, this subquery is evaluated on each step:

实际上,每个步骤都会对此子查询进行评估:

EXISTS
(
SELECT  NULL
FROM    [...]
WHERE   id = table1.id
)

You can actually see it in the detailed description provided by EXPLAIN EXTENDED.

您可以在EXPLAIN EXTENDED提供的详细说明中看到它。

That's why it's called DEPENDENT SUBQUERY: the result of each evaluation depends on the value of table1.id. The subquery as such is not correlated, it's the optimized version that is correlated.

这就是为什么它被称为DEPENDENT SUBQUERY:每次评估的结果取决于table1.id的值。子查询本身不相关,它是相关的优化版本。

MySQL always evaluates the EXISTS clause after the more simple filters (since they are much easier to evaluate and there is a probability that the subquery won't be evaluated at all).

MySQL总是在更简单的过滤器之后评估EXISTS子句(因为它们更容易评估,并且有可能根本不评估子查询)。

If you want the subquery to be evaluated all at once, rewrite the query as this:

如果要一次性评估子查询,请按以下方式重写查询:

SELECT  table_1.*
FROM    (
        SELECT  DISTINCT id
        FROM    [...]
        ) q
JOIN    table_1
ON      table_1.id = q.id
JOIN    table_2
ON      [...]
JOIN    table_3
ON      [...]
WHERE   [more conditions]

This forces the subquery to be leading in the join, which is more efficient if the subquery is small compared to table_1, and less efficient if the subquery is large compared to table_1.

这会强制子查询在连接中处于领先地位,如果子查询与table_1相比较小,则效率更高,如果子查询与table_1相比较大,则效率较低。

If there is an index on [...].id used in the subquery, the subquery will be performed using an INDEX FOR GROUP-BY.

如果子查询中使用了[...]。id的索引,则子查询将使用INDEX FOR GROUP-BY执行。

#2

this is a known bug in mysql: http://bugs.mysql.com/bug.php?id=25926

这是mysql中的一个已知错误:http://bugs.mysql.com/bug.php?id = 25926

a useful workaround is to push down the subquery into another select * from (subquery) as dt type subquery.

一个有用的解决方法是将子查询下推到另一个select * from(子查询)作为dt类型子查询。

#3

You could select the subquery's result into a temporary table first and then join to it in the main query.

您可以先将子查询的结果选择到临时表中,然后在主查询中加入它。

#4

The best way is to put the WHERE conditions for table1 into a subquery in the FROM clause. EG:

最好的方法是将table1的WHERE条件放入FROM子句中的子查询中。例如:

SELECT `table_1`.* 
FROM (
      SELECT * FROM `table_1` WHERE `table_1`.`id` IN (...)
     )
  INNER JOIN `table_2` [...]
  INNER JOIN `table_3` [...]
WHERE [more conditions]

#5

-1

Unfortunately, no, you cannot. Subqueries are actually run once for each row in the outer query.

不幸的是,不,你不能。子查询实际上为外部查询中的每一行运行一次。

I'd actually suggest that you convert this to another join, using table_1.id as your key to the other table.

我实际上建议您将此转换为另一个连接,使用table_1.id作为另一个表的键。

#1

SELECT  `table_1`.*
FROM    `table_1`
INNER JOIN
        `table_2` [...]
INNER JOIN
        `table_3` [...]
WHERE   `table_1`.`id` IN
        (
        SELECT  `id`
        FROM    [...]
        )
        AND [more conditions]