MySQL Forums
Forum List  »  Optimizer & Parser

Primary key index with a DATETIME as first part of the compound key is never used
Posted by: jeremy cohen solal
Date: December 30, 2011 08:10AM

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 SIMPLE stats ALL NULL NULL NULL NULL 8832329 Using where


Same request on the other table stats_todays (With INTEGER and TO_DAYS() )


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

=> Result 1 row (0.0003 sec)

Explain:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stats_todays const PRIMARY PRIMARY 13 const,const,const,const,const,const 1

If you read the full post, you understand that is not a low cardinality problem since the request work with exactly the same cardinality with an INTEGER dateDim field....


Here is some advanced details:

SELECT COUNT( DISTINCT dateDim )
FROM stats_todays
UNION ALL
SELECT COUNT( DISTINCT dateDim )
FROM stats;

Result:


COUNT(DISTINCT dateDim)
2192
2192

Here is the INDEX description:

SHOW INDEXES FROM `stats`

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
stats 0 PRIMARY 1 dateDim A 6921 NULL NULL BTREE
stats 0 PRIMARY 2 accountDim A 883232 NULL NULL BTREE
stats 0 PRIMARY 3 execCodeDim A 8832329 NULL NULL BTREE
stats 0 PRIMARY 4 operationTypeDim A 8832329 NULL NULL BTREE
stats 0 PRIMARY 5 junkDim A 8832329 NULL NULL BTREE
stats 0 PRIMARY 6 ipCountryDim A 8832329 NULL NULL BTREE

SHOW INDEXES FROM `stats_todays`

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
stats_todays 0 PRIMARY 1 dateDim A 7518 NULL NULL BTREE
stats_todays 0 PRIMARY 2 accountDim A 4022582 NULL NULL BTREE
stats_todays 0 PRIMARY 3 execCodeDim A 8045164 NULL NULL BTREE
stats_todays 0 PRIMARY 4 operationTypeDim A 8045164 NULL NULL BTREE
stats_todays 0 PRIMARY 5 junkDim A 8045164 NULL NULL BTREE
stats_todays 0 PRIMARY 6 ipCountryDim A 8045164 NULL NULL BTREE


SELECT dateDim, COUNT(*) FROM stats GROUP BY dateDim WITH ROLLUP

* tells there is 2192 different dates, and the repartition is smooth (approx 3000 - 4000 rows by date)
* there is 8 831 990 rows in the table
* The same for the other table
* I tried with COVERING INDEX (replacing * by all PK columns) => nothing changed
* I tried force|use index => nothing changed
* The same with date field instead datetime
* The same with INDEX or UNIQUE instead of primary key

Options: ReplyQuote


Subject
Views
Written By
Posted
Primary key index with a DATETIME as first part of the compound key is never used
4207
December 30, 2011 08:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.