MySQL - AND condition

Question

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 :)

Answer 1

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

Answer 2

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.

Answer 3

I would not count on that : Where Optimisations. That link explains that other criterias prevail on the order.

Answer 4

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.