How Does WHERE Clause Condition Ordering Affect MySQL Query Performance?

The Impact of WHERE Clause Condition Ordering on MySQL Performance

When crafting complex queries with multiple conditions, the order of those conditions can potentially influence MySQL's performance. A particular condition, such as filtering by company ID, can significantly reduce the number of rows examined.

Question:

Consider the following two queries with differing condition ordering:

Query 1:

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

Query 2:

 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) 

Does the order of these conditions impact MySQL's efficiency?

Answer:

The order of WHERE clause conditions can indeed affect MySQL performance under certain circumstances. Consider the following example:

Query 3:

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

Query 4:

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

The only difference between these queries is the order of operands in the OR condition. "myslowfunction" is a function that intentionally delays for a second. The query execution reveals that:

• Query 3: myslowfunction is called four times (myint values 1, 2, 3, 4).
• Query 4: myslowfunction is called seven times (myint values 1, 2, 3, 4, 5, 6, 7).

This demonstrates that when a slow function appears on the left side of an OR condition with an operand that isn't always true, the function is executed more often.

Conclusion:

In summary, the order of conditions in a WHERE clause can influence MySQL performance due to short-circuiting. To optimize performance, place conditions that are expected to significantly reduce the number of rows examined (e.g., company ID) as early as possible in the condition chain.

The above is the detailed content of How Does WHERE Clause Condition Ordering Affect MySQL Query Performance?. For more information, please follow other related articles on the PHP Chinese website!