List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:March 20 2014 2:10am
Subject:Re: Precedence in WHERE clauses.
View as plain text  

On 3/19/2014 7:27 PM, Mogens Melander wrote:
> G'morning
>
> A function in a where equals what ?
>
> On Wed, March 19, 2014 15:05, Morgan Tocker wrote:
>> Hi Christophe,
>>
>>> Considering the following simple query :
>>>
>>> SELECT * FROM Status WHERE DWProcessed = 0 AND PreviousStatus NOT IN
>>> ('PENDING', 'ACCEPTED') AND SubscribeDate < DATE_SUB(NOW(), INTERVAL 24
>>> HOUR);
>>>
>>> Which of these filters are processed first ?
>>>
>>> I'd like the first filter (DWProcessed / Lowest cardinality and indexed)
>>> being processed first, but I can't really find any useful information
>>> about this .
>>>
>>> Is there any performance impact on query processing, about the order of
>>> WHERE clauses ?
>>
>>
>> When a MySQL server receives a query, it goes through a process called
>> query optimization and tries to determine the best way to execute it
>> (based on availability of indexes etc).  You can think of this as similar
>> to how GPS software picks the fastest route - it is very similar.
>>
>> The order of the WHERE clause does not matter, and in fact more
>> complicated transformations happen in query optimization automatically.
>> For Example:
>> SELECT * FROM Status WHERE 1=1 AND DWProcessed = 0;
>>
>> 1=1 is detected as a tautology and removed.
>> --

Hello Mogens,

The entire WHERE expression becomes a row-by-row TRUE/FALSE test for 
which rows should become part of the results. The expressions are 
evaluated in whichever order the Optimizer thinks will get us to the 
answer with the least effort.

In this particular query case, there are three tests to perform
1) DWProcessed = 0
2) PreviousStatus NOT IN ('PENDING', 'ACCEPTED')
3) SubscribeDate < DATE_SUB(NOW(), INTERVAL 24 HOUR)

The NOT IN test of #2 is actually shorthand for a negated OR expression. 
It is evaluated like this

(PreviousStatus <> 'PENDING') AND (PreviousStatus <> 'ACCEPTED')

According to the original WHERE clause, all three terms are AND-ed 
together which means that if any one of those tests fail for a 
particular row, that row does not become part of the final result.

The inner workings of the optimizer are complex. There is quite a bit 
already written about how it works. And, you can review that section of 
the code if you are truly adventurous . MySQL is open source and it's 
all there for anyone to review.

I also suggest that a good introduction to the entire process is in 
sections 8.2.1.2 through 8.2.1.14 of the manual. These provide a nice 
overview of the various access and simplification methods that the 
Optimizer can choose from while trying to work out the optimal method of 
resolving a SQL command.
https://dev.mysql.com/doc/refman/5.6/en/where-optimizations.html

Best wishes,
-- 
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
Thread
Precedence in WHERE clauses.Christophe18 Mar 2014
  • Re: Precedence in WHERE clauses.Michael Dykman18 Mar 2014
    • Re: Precedence in WHERE clauses.Christophe20 Mar 2014
      • Re: Precedence in WHERE clauses.Roy Lyseng20 Mar 2014
  • Re: Precedence in WHERE clauses.Glyn Astill19 Mar 2014
  • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
    • Re: Precedence in WHERE clauses.Reindl Harald19 Mar 2014
      • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
        • Re: Precedence in WHERE clauses.Reindl Harald19 Mar 2014
          • Re: Precedence in WHERE clauses.Morgan Tocker19 Mar 2014
            • Re: Precedence in WHERE clauses.Christophe20 Mar 2014
              • Re: Precedence in WHERE clauses.Christophe23 Mar 2014
                • Re: Precedence in WHERE clauses.shawn l.green25 Mar 2014
    • Re: Precedence in WHERE clauses.Mogens Melander19 Mar 2014
      • Re: Precedence in WHERE clauses.shawn l.green20 Mar 2014