Does the order of conditions in a WHERE clause affect MySQL performance?

Question

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)  

Answer 1

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.

Answer 2

Here is a demo showing the order of WHERE clause conditions can make a difference due to short-circuiting. It runs the following queries:

-- query #1 SELECT myint FROM mytable WHERE myint >= 3 OR myslowfunction('query #1', myint) = 1;  -- query #2 SELECT myint FROM mytable WHERE myslowfunction('query #2', myint) = 1 OR myint >= 3; 

The only difference between these is the order of operands in the OR condition.

myslowfunction deliberately sleeps for a second and has the side effect of adding an entry to a log table each time it is run. Here are the results of what is logged when running the two queries:

myslowfunction called for query #1 with value 1 myslowfunction called for query #1 with value 2 myslowfunction called for query #2 with value 1 myslowfunction called for query #2 with value 2 myslowfunction called for query #2 with value 3 myslowfunction called for query #2 with value 4 

The above shows that a slow function is executed more times when it appears on the left side of an OR condition when the other operand isn't always true.

So IMO the answer to the question:

Does the order of conditions in a WHERE clause affect MySQL performance?

is "Sometimes it can do."