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

by

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.”

More Related Contents:

  1. SQL select only rows with max value on a column [duplicate]
  2. How to create a MySQL hierarchical recursive query?
  3. How to reset AUTO_INCREMENT in MySQL
  4. MYSQL OR vs IN performance
  5. SELECTING with multiple WHERE conditions on same column
  6. MySQL Error: : ‘Access denied for user ‘root’@’localhost’
  7. How to find gaps in sequential numbering in mysql?
  8. MySQL: Invalid use of group function
  9. Combining UNION and LIMIT operations in MySQL query
  10. MySQL comparison with null value
  11. MySQL Like multiple values
  12. Matching all values in IN clause
  13. MySql export schema without data
  14. INTERSECT in MySQL
  15. How do I get the ID of multiple inserted rows in MySQL?
  16. How to alter a column and change the default value?
  17. limiting the rows to where the sum a column equals a certain value in MySQL
  18. MySql Transpose Row into Column and Column into Row [duplicate]
  19. MySQL, Concatenate two columns
  20. MySQL: Inner join vs Where [duplicate]
  21. Syntax error when using except in a query
  22. Selecting from two tables with inner join and limit [duplicate]
  23. Why the order of evaluation for expressions involving user variables is undefined?
  24. sql like operator on integer
  25. How to select data where a field has a min value in MySQL?
  26. MySQL – SELECT * INTO OUTFILE LOCAL ?
  27. How to SELECT by MAX(date)?
  28. The best way to remove value from SET field?
  29. get a recursive parent list
  30. SQL query with avg and group by

Leave a Comment