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.

Answer 2

The place to start investigating is with explain, which will show you what the optimizer does for your particular query.

Several examples are available for study; these seem useful:

• Using EXPLAIN to Write Better MySQL Queries
• Explaining MySQL's EXPLAIN
• Analyzing Queries for Speed with EXPLAIN

Given those tools, others state that MySQL does short-circuit queries:

• MySQL 12.3.3 Logical Operators developer's comment says

  The documentation doesn't say anything about this, but it appears (based on some tests I just ran) that MySQL short-circuits evaluation of logical operators just like most other languages do.

although the related order-by may not be:

• Does MySQL short-circuit the ORDER BY clause?

However, as noted in the MySQL documentation comments, the documentation itself is not explicit. You would have to use the appropriate tools and rely upon repeatable behavior of the optimizer to determine the actual state of affairs.