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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
