Does MySQL order matter for short-circuiting predicates?
Let's say I need to run a SQL query:
SELECT data FROM table WHERE condition1 AND condition2 AND condition3 AND condition4
This is nothing but
SELECT data FROM table WHERE condition3 AND condition1 AND condition4 AND condition2
?
If it doesn't:
I know from my own experience that condition1
cheaper than condition2
cheaper than condition3
cheaper than condition4
.
If any of the previous conditions are not met, the remaining conditions should never be checked. The optimizer will not be immediately apparent since the stored functions are involved. How do I write a query that does this?
source to share
In practice, MySQL probably evaluates conditions in order - if you only have one table per query. If the conditions are between the tables, then all bets are disabled.
In some respects, your question is not clear. If some conditions can be resolved at compile time, they will often be (see here ). Once an expression evaluates to FALSE on the string AND
s, then there is no need to further evaluate the expressions.
Without explicit documentation, you can opt out of defining ANSI grading order. Here is a question about this particular topic. This basically means that there is no guarantee on the order of ratings. This assumes that:
where ((((condition1 and condition2) and condition3) and condition4)
guarantees evaluation in a specific order. I suspect that the excess parentheses will be dropped during the compile phase and the ANSI condition is not as clear as it sounds.
The only expression that guarantees the order of evaluation in SQL is an expression case
. Although I'm not a fan of the use case
in the sentence where
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 ensures the order of the evaluation.
By the way, for operators and functions in MySQL, the time for an operation does not matter, compared to the time for retrieving rows. There are, of course, exceptions. In particular, any pattern matching on long strings would be quite expensive. And function calls can be very expensive.
source to share
A place to start research is explain
, which will show you what the optimizer does for your specific query.
Several examples are available for study; they seem to be helpful:
- Using EXPLAIN to Record Better MySQL Queries
- MySQL EXPLAIN Explanation
- Analyzing Requests for Speed with EXPLAIN
Given these tools, others have argued that MySQL does short-circuiting queries:
- MySQL 12.3.3 Boolean Operators developer comment
The documentation doesn't say anything about it, but it does appear (based on some tests I just ran) that MySQL has short-circuited the evaluation of logical operators like most other languages.
although the associated order cannot be:
However, as noted in the comments on the MySQL documentation, the documentation itself is not explicit. You will need to use the appropriate tools and rely on repeatable optimizer behavior to determine the actual state of affairs.
source to share