Does order matter in MySQL for short circuiting of predicates?

Question

Let's say I have to run the SQL query:

SELECT data FROM table WHERE condition1 AND condition2 AND condition3 AND condition4 

Is that any different than

SELECT data FROM table WHERE condition3 AND condition1 AND condition4 AND condition2

?

---

If it's not different:

I know from my own experience that condition1 is less expensive than condition2 is less expensive than condition3 is less expensive than condition4.

If any of the prior conditions are not met, the remaining conditions should never be checked. It wouldn't be immediately obvious to the optimizer, as stored functions are involved. How should I write a query that does this?

Answer 1

In practice, MySQL probably evaluates the conditions in order -- assuming that you have only one table in the query. If the conditions are between tables, then all bets are off.

Your question is unclear in some respects. If some of the conditions can be resolved at compile-time, then they often will (see here). Once an expression evaluates to FALSE in a chain of ANDs, then it doesn't need to further evaluate expressions.

Without explicit documentation, you can fall back on the ANSI definition of evaluation order. Here is a question about that specific topic. This basically says that there is no guarantee on the order of evaluations. It does suggest that:

where ((((condition1 and condition2) and condition3) and condition4)

would guarantee evaluation in a particular order. I suspect that redundant parentheses would be dropped during the compile phase, and the ANSI condition is not quite as clear as it seems.

The only expression that guarantees order of evaluation in SQL is the case expression. Although I am not a fan of using case in the where clause you could do:

where (case when not condition1 then 0
            when not condition2 then 0
            when not condition3 then 0
            else not condition4
       end)

This would guarantee order of evaluation.

By the way, for the operators and functions in MySQL, the time for an operation is not going to matter, compared to the time for retrieving the rows. Of course, there are exceptions. In particular, any pattern matching on long strings is going to be rather expensive. And, calls to functions may be very expensive as well.