Is using DATE in SELECT query on a DATETIME indexed column in mysql correct with respect to optimization?

Question

I have a very huge table (425+ million rows).

CREATE TABLE `DummyTab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(48) NOT NULL,
  `BeginDate` datetime DEFAULT NULL,
  `EndDate` datetime NOT NULL,
  ......
  ......
  KEY `BeginDate_index` (`dBegDate`),
  KEY `id` (`id`),
) ENGINE=MyISAM

Selects are done based on "BeginDate" and other criteria on this table

select * from DummyTab where Name like "%dummyname%" and BeginDate>= 20141101

Now in this case only the date field is being provided out of datetime (although it'll be used as 2014-11-01 00:00:00).

Question is DOES THE OPTIMIZER MAKE USE OF DATETIME INDEX PROPERLY EVEN WHEN JUST DATE IS PROVIDED IN THIS CASE ? or should the index be set on a "date" field to be used more effectively rather than a "datetime"

Answer 1

Yes, BeginDate_index can still be used when the query is specified with a DATE-only filter (also applying additional criteria on Name won't disqualify the index either).

If you look at this SqlFiddle of random data, and expand the Execution plan at the bottom, you'll see something like:

ID   SELECT_TYPE  TABLE  TYPE     POSSIBLE_KEYS    KEY             KEY_LEN   REF    ROWS   FILTERED                EXTRA
1    SIMPLE       DummyTab range  BeginDate_index  BeginDate_index 6         17190  100    Using index condition;  Using where   

(Specifically KEY is BeginDate_index). Note however that use of the index is not guaranteed, e.g. if you execute the same query against a wider range of date criteria, that a different plan may be used (e.g. if you run the same fiddle for > 20140101, the BeginDate_index is no longer used, since it does not offer sufficient selectivity).

Edit, Re: Comment on Exactness
Since BeginDate is a datetime, the literal 20141101 will be also be converted to a Datetime (once). From the docs:

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed.

So again, yes, as per your last paragraph, the literal in the filter BeginDate >= 20141101 will be converted to the exact date time 20141101000000 (2014-11-01 00:00:00) and any eligible indexes will be considered (but again, never guaranteed).

A common issue where indexes cannot be used is because the filter predicates are NOT sargable is when a function is applied to a column in a filter, as the engine would need to evaluate the function on all remaining rows in the query. Some examples here.

So altering your example a bit, the below queries do the same thing, but the second one is much slower. This query is sargable:

SELECT * FROM DummyTab
WHERE BeginDate < 20140101;  -- Good

Whereas this is NOT:

SELECT * FROM DummyTab
WHERE YEAR(BeginDate) < 2014; -- Bad

Updated SqlFiddle here - again, look at the Execution Plans at the bottom to see the difference.