在JOIN子句中,MySQL逻辑评估是惰性的还是短路的? [英] Is MySQL logic evaluation lazy/short-circuiting in JOIN clause?
问题描述
采用以下表达式:FALSE AND (expression)
MySQL会评估表达式还是在看到FALSE
时继续前进?
Will MySQL evaluate the expression or just move on as soon as it sees FALSE
?
一些背景上下文-我想通过以下方法来加快查询速度:
Some background context-- I wanted to speed up a query by doing:
JOIN... ON (indexed_column1=indexed_column2 AND non_indexed_column_a=non_indexed_column_b)
有关为何执行此查询的背景信息请参阅此答案
For background on why I'm doing this query see this answer
如果要始终评估non_indexed_column_a=non_indexed_column_b
,则不会节省任何时间.
If it's going to always evaluate non_indexed_column_a=non_indexed_column_b
then no time is saved with that.
推荐答案
MySQL查询优化器尽可能使用索引,并使用限制性最强的索引以消除尽可能多的行.
The MySQL query optimizer uses indexes whenever possible and to use the most restrictive index in order to eliminate as many rows as possible.
因此,对于您的查询,它将始终根据第一个索引列过滤记录,然后从非索引列过滤记录.
So in case of your query it will always filter the records based on first indexes columns and then filter the records from non-index columns.
Also before query execution, MySQL eliminates the code which is always going to be false (Dead Code) .
有关更多详细信息,请参见: http://www.informit .com/articles/article.aspx?p = 377652& seqNum = 2
For more details see: http://www.informit.com/articles/article.aspx?p=377652&seqNum=2
这篇关于在JOIN子句中,MySQL逻辑评估是惰性的还是短路的?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!