7

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

Alex
  • 66,732
  • 177
  • 439
  • 641

5 Answers5

4

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

LukeH
  • 263,068
  • 57
  • 365
  • 409
  • 2
    According to the SQL spec, this behaviour is implementation-dependent: http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated/909770#909770 – AgentConundrum Dec 15 '10 at 10:58
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.

jensgram
  • 31,109
  • 6
  • 81
  • 98
2

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

Vincent Mimoun-Prat
  • 28,208
  • 16
  • 81
  • 124
1

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.

Linus Kleen
  • 33,871
  • 11
  • 91
  • 99
0

If it does short-circuit when first condition fails( which is most likely ), it would be best to put those conditions, that are most likely to fail, first!

let's say we have 3 conditions, and all must be true( separated by "AND" ). slow case: 1. never fail. All rows are looked through and success. 2. sometimes fail. All rows are looked through and still success. 3. often fail. All rows are looked through and this time we fail. Result: It took a while, but can't find a match.

fast case: 1. often fail. All rows are looked through and matching fail. 2. sometimes fail. NOT looked through, because searching ended due to short-circuit. 3. never fail. NOT looked through, because searching ended due to short-circuit. Result: Quickly figured, no match.

Correct me if I'm wrong. I can imagine, that all conditions are checked, for each row looked ad. Which makes this matter ALOT less. If your fields are ordered in the same order, as your conditions, you could maybe measure the difference.

user305874
  • 19
  • 2