Primary key index with a DATETIME as first part of the compound key is never used

I have a problem with INDEXING a DATETIME (or even a date) as first part of my PRIMARY KEY.

I use MySQL 5.5

Here are my two tables:

-- This is my standard table with dateDim as a dateTime

CREATE TABLE `stats` (
 `dateDim` datetime NOT NULL,
 `accountDim` mediumint(8) unsigned NOT NULL,
 `execCodeDim` smallint(5) unsigned NOT NULL,
 `operationTypeDim` tinyint(3) unsigned NOT NULL,
 `junkDim` tinyint(3) unsigned NOT NULL,
 `ipCountryDim` smallint(5) unsigned NOT NULL,
 `count` int(10) unsigned NOT NULL,
 `amount` bigint(20) NOT NULL,
 PRIMARY KEY (`dateDim`,`accountDim`,`execCodeDim`,`operationTypeDim`,`junkDim`,`ipCountryDim`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- Here is a copy with datDim as an integer

CREATE TABLE `stats_todays` (
`dateDim` int(11) unsigned NOT NULL,
 `accountDim` mediumint(8) unsigned NOT NULL,
 `execCodeDim` smallint(5) unsigned NOT NULL,
 `operationTypeDim` tinyint(3) unsigned NOT NULL,
 `junkDim` tinyint(3) unsigned NOT NULL,
 `ipCountryDim` smallint(5) unsigned NOT NULL,
 `count` int(10) unsigned NOT NULL,
 `amount` bigint(20) NOT NULL,
 PRIMARY KEY (`dateDim`,`accountDim`,`execCodeDim`,`operationTypeDim`,`junkDim`,`ipCountryDim`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I fill both table with exactly the same data (near 10 000 000)

But:

• stats table use a DATETIME for dateDim

• stats_todays use un INTEGER with TO_DAYS() for dateDim

My question is: why MySQL does'nt USE the PRIMARY KEY when the first part of the index is a datetime ???
It is very strange since With the same data but consolidated with an INTEGER and TO_DAYS(dateDim) the same request rocks....

Example with stats table (and datetime):

``
SELECT *
FROM stats`
WHERE
dateDim = '2014-04-03 00:00:00'
AND accountDim = 4
AND execCodeDim = 9
AND operationTypeDim = 1
AND junkDim = 5
AND ipCountryDim = 3

=> 1 result (4.5sec)

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIM

This is a bug in 5.5.x. See here

That suggests that your query should be

SELECT * 
FROM `stats`  
WHERE 
   dateDim = CAST('2014-04-03 00:00:00' as datetime)
   AND accountDim = 4
   AND execCodeDim = 9
   AND operationTypeDim = 1
   AND junkDim = 5
   AND ipCountryDim = 3

StackExchange Database Administrators Q#10340, answer score: 6