Menu
Let's say I have a query like this:
SELECT bla WHERE foo LIKE '%bar%' AND boo = 'bar' AND whatvr IN ('foo', 'bar')...
I was wondering if MySQL continues to check all conditions when retrieving results.
For eg. if foo is not LIKE %bar%, will it continue to check if boo = 'bar', and so on ?
Would it be any faster if I put conditions that are less likely to be true at the end?
I'm sorry if this seems to be stupid question, I'm a complete noob when it comes to SQL :)
993
MySQL AND, OR and NOT OperatorsThe AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE.
The MySQL IN condition is used to reduce the use of multiple OR conditions in a SELECT, INSERT, UPDATE and DELETE statement.
The difference between AND, OR is that AND evaluates both conditions must be true for the overall condition to be true. The OR evaluates one condition must be true for the overall condition to be true. In the OR result, if name is John then condition will be true. If any row has the age 22, then it will be true.
MySQL logical AND operator compares two expressions and returns true if both of the expressions are true. Syntax: AND, && This operator returns 1 if all operands are nonzero and not NULL, 0 if one or more operands are 0, otherwise, NULL is returned.
I don't think there are any guarantees about whether or not multiple conditions will be short-circuited, but...
In general, you should treat the query optimiser as a black box and assume -- unless you have evidence to the contrary -- that it will do its job properly. The optimiser's job is to ensure that the requested data is retrieved as efficiently as possible. If the most efficient plan involves short-circuiting then it'll do it; if it doesn't then it won't.
(Of course, query optimisers aren't perfect. If you have evidence that a query isn't being executed optimally then it's often worth re-ordering and/or re-stating the query to see if anything changes.)
199
What you're looking for is documentation on MySQL's short-circuit evaluation. I have, however, not been able to find anything better than people who were not able to find the documentation, but they claim to have tested it and found it to be true, i.e., MySQL short-circuits.
Would it be any faster if I put conditions that are less likely to be true at the end?
No, the optimizer will try and optimize (!) the order of processing. So, as for the order of tests, you should not assume anything.
44
I would not count on that : Where Optimisations. That link explains that other criterias prevail on the order.
2
You can't rely on MySQL evaluating conditions from left to right (as opposed to any programming language). This is because the "WHERE clause optimizer" looks for columns that are indexed and will look for this subset first.
For query optimization see the chapter Optimizing SELECT Statements in the MySQL reference manual.
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
