WHERE子句中条件的顺序是否会影响MySQL的性能? [英] Does the order of conditions in a WHERE clause affect MySQL performance?

问题描述

说我有一个漫长而昂贵的查询，里面挤满了条件，要搜索大量的行.我还有一个特殊条件，例如公司ID，它将限制需要大量搜索的行数，将其范围从数十万缩小到数十.

Say that I have a long, expensive query, packed with conditions, searching a large number of rows. I also have one particular condition, like a company id, that will limit the number of rows that need to be searched considerably, narrowing it down to dozens from hundreds of thousands.

是否执行此操作对MySQL性能有影响吗?

Does it make any difference to MySQL performance whether I do this:

 SELECT * FROM clients WHERE 
       (firstname LIKE :foo OR lastname LIKE :foo OR phone LIKE :foo) AND 
       (firstname LIKE :bar OR lastname LIKE :bar OR phone LIKE :bar) AND 
       company = :ugh

或者这个:

 SELECT * FROM clients WHERE 
       company = :ugh AND
       (firstname LIKE :foo OR lastname LIKE :foo OR phone LIKE :foo) AND 
       (firstname LIKE :bar OR lastname LIKE :bar OR phone LIKE :bar) 

推荐答案

否，顺序应该不会有太大的不同.当找到符合条件的行时，将针对每一行检查整个条件(通过布尔逻辑组合的所有子条件).

No, the order should not make a large difference. When finding which rows match the condition, the condition as a whole (all of the sub-conditions combined via boolean logic) is examined for each row.

一些智能数据库引擎将尝试猜测条件的哪些部分可以更快地进行评估(例如，不使用内置函数的事物)，然后先评估那些，然后再评估更复杂(估计)的元素.不过，这是由数据库引擎而非SQL确定的.

Some intelligent DB engines will attempt to guess which parts of the condition can be evaluated faster (for instance, things that don't use built-in functions) and evaluate those first, and more complex (estimatedly) elements get evaluated later. This is something determined by the DB engine though, not the SQL.

这篇关于WHERE子句中条件的顺序是否会影响MySQL的性能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！