List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:March 25 2014 6:54pm
Subject:Re: Precedence in WHERE clauses.
View as plain text  
Hello Christophe,

On 3/23/2014 2:34 PM, Christophe wrote:
> Hi all,
>
> Le 20/03/2014 13:20, Christophe a écrit :
>> Hi Morgan, Harald, and all,
>>
>> Thanks for the answers.
>>
>> While reading your answers, I realize one more time this problem does
>> not seem so obvious ... That's why I asked ;) .
>>
>> Beyond the specific case I mentioned, my goal is also to get a general
>> discussion about theorical behaviour of MySQL while handling WHERE clauses.
>>
>> I don't have access to the application for now, but will try EXPLAIN as
>> soon as I can .
>
> Here is the Result of Explain :
>
> http://perso.stux6.net/mysql/20140323/EXPLAIN.txt
>
> I don't really know what is tells in details, but according to "key"
> field, it seems the IDX_CS_DWProcessed key is processed first, as I'd
> like it to be ...
>
> Other question : is an INDEX on DATETIME field really useful ?
>

Yes, they can be quite useful depending on selective they are. In fact, 
you may want to create a combination index that contains the 'processed' 
flag as one of the first or final fields (depending on how you use it) 
to make an index that is even more selective than one you have already. 
Normally a flag field like 'processed' has only 2 values: yes or no. 
Yours may contain multiple other values (I don't know) so the 
cardinality on that index may have made it the most selective choice for 
this particular query.

http://dev.mysql.com/doc/refman/5.6/en/execution-plan-information.html
http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html
http://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

The general rule is, a table scan (sequential access) will be more 
efficient than an indexed lookup of rows (random access) if the indexed 
retrieval will need to get more than about 30% of the rows of a table.

Yours,
-- 
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