ON 子句的两个位置:哪个更快? [英] Two positions of ON clause: Which is faster?

问题描述

以这种方式加入给定的选择会更快吗

选择...连接表1条件 1连接表2条件 2

然后这个

选择...连接表1连接表2条件 1和条件2

因为你保持临时表更小?

(让我们假设:condition1 与 table2 无关.)

解决方案

理论上，过滤条件应该尽快"说明，即选项 1 可能表现更好.>

在实践中，大多数时候，SQL 解析器会将任一版本优化为相同的执行计划.也就是说，除非你的条件真的很扭曲，或者 table1 或 table2 实际上是子查询.

如果您有实际的查询要玩，请检查执行计划:EXPLAIN [your query here]

(抱歉，如果你不给我们提供更多……细节，就很难说得更具体了)

Would it be faster to join a given select this way

select ...
join table1 
on condition1
join table2
on condition2

then this

select ...
join table1
join table2
on condition1
and condition2

because you keep the temp-tables smaller?

(Let us assume: condition1 has nothing to do with table2.)

解决方案

In theory, filter conditions should be stated "as soon as possible", that is, option 1 may perform better.

In practice, most of the time, the SQL parser will optimize either version to the same execution plan. That is, unless your conditions are really twisted, or table1 or table2 are actually subqueries.

If you have actual queries to play with, check the execution plan: EXPLAIN [your query here]

(sorry, it is hard to be more specific if you don't feed us with more... specifics)

这篇关于ON 子句的两个位置:哪个更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！