17

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
10
  • This is strange indeed. Does the same happens if you use date instead of datetime ? Dec 30, 2011 at 13:38
  • yes it does exactly the same
    – nemenems
    Dec 30, 2011 at 13:38
  • 1
    And if you run WHERE dateDim = DATE('2014-04-03 00:00:00') ? Dec 30, 2011 at 13:43
  • 1
    With a reorder of the pk it works. But in facts, I want to make a request with only dateDim and accountDim in the where clause. I use all the pk fields for the case study...
    – nemenems
    Dec 30, 2011 at 13:48
  • 1
    WHERE dateDim = DATE('2014-04-03 00:00:00') => nothing changed
    – nemenems
    Dec 30, 2011 at 13:49

2 Answers 2

6

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
1

Since the int version of the table

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 

works fine in terms of the query, you should have dateDim contain the UNIX_TIMESTAMP() of the datetime string. You query would look more like this:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.