mysql where 条件顺序,WHERE子句中的条件顺序是否会影响MySQL性能？

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.

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

or this:

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.

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.