Simple MySQL indexing issue

Question

I ve got this table:

CREATE TABLE IF NOT EXISTS `test1_nopart` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `idAccount` int(10) unsigned NOT NULL,
  `data` mediumint(8) unsigned NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `date` (`date`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

I fill this table with 10 000 000 rows. The repartition by date is homogenous

EXPLAIN SELECT * FROM `test1_nopart` WHERE date = "2014-03-04" 

Here is the result

id  select_type   table        type     possible_keys   key     key_len     ref     rows        Extra
1   SIMPLE     test1_nopart     ALL     NULL            NULL    NULL        NULL    7875981     Using where

=> without the explain 3.6sec for a result of 3000 rows (approx)

As you can see the index is not used and it is not part of the possible_keys column!

the same request with a covering index way

EXPLAIN SELECT date FROM `test1_nopart` WHERE date = "2014-03-04"

the result:

id  select_type     table      type     possible_keys   key     key_len     ref     rows        Extra
1   SIMPLE       test1_nopart   index   NULL            date       3        NULL    7875981     Using where; Using index

=> without the explain 2.8sec for a result of 3000 rows (approx)

Why MySQL doesn't use properly this index(DATE) ???

Info: - VM Server (our dev environment, I don't know what is the hardware composition) - MySQL 5.5.8

SHOW INDEX FROM test1_nopart

result:

Table   Non_unique  Key_name    Seq_in_index    Column_name     Collation   Cardinality     Sub_part    Packed  Null    Index_type  Comment     Index_comment
test1_nopart    0   PRIMARY     1   id  A   7875981     NULL    NULL        BTREE        
test1_nopart    1   date    1   date    A   6077    NULL    NULL        BTREE        

• For date 2014-03-04 => 3134 rows
• The total (rollup) => 7 875 488
• There is 2556 differents 'date' values in the table

Answer 1

The MySQL Query Optimizer sees that the Index Traversal of the date index includes a dive into the Clustered Index (known internally as gen_clust_index). In light of this, the MySQL Query Optimizer figured that it is easier to perform a full table scan in the first query, and a full index scan in the scecond query.

You may also need to see the cardinality of the index and how many rows each distinct value has.

Perform the following:

SELECT COUNT(1) datecount,`date` FROM test1_nopart GROUP BY `date` WITH ROLLUP;

According to your comment, you get 6077 distinct rows. You also said there are about 10,000,000 rows. Run this query instead:

SELECT COUNT(1) datecount FROM test1_nopart WHERE `date` = '2014-03-14';

Please note the counts and the total.

5% of 10,000,000 is 500,000

If there are more that 500,000 rows with the date '2014-03-14', then MySQL will never use the index properly for that particular value.

I do not trust SHOW INDEXES FROM test1_nopart; because the table is InnoDB. MyISAM would show exact number. InnoDB produces numbers based on Dives into the Index.

If the datecount of any date exceeds 5% of the total number of rows, MySQL Query Optimizer will throw up its hands and do a full scan.

UPDATE

OK that 5% rule of thumb is out the window. Try fooling the MySQL Query Optimizer by creating a different covering index:

ALTER TABLE test1_nopart ADD INDEX date_id_ndx (`date`,id);

and try your queries out again.

Answer 2

Just a hunch - maybe it has something to do with the word date.

Try to give some hints to MySQL that you want to use the field, not the reserved word:

SELECT date FROM `test1_nopart` WHERE `test1_nopart`.`date` = "2014-03-04"

Answer 3

Here is my thoughts.

In the first case when we tries to get data by date MySQL doesn't use index on date because of very low cardinality. And optimizer use the following: - secondary index - clustered to access to row - table to get data.

In the second case when we tries to get date by date it easier to go thru table using index because MySQL can retrieve selecting data from index too (I mean MySQL can scan just index instead of whole table to get the same data). Use the following: - secondary index

Answer 4

It is not a problem of cardinality.

I made a lot of test and I made another post describing the problem.

https://stackoverflow.com/questions/8679940/primary-key-index-with-a-datetime-as-first-part-of-the-compound-key-is-never-use

The problem occured ONLY when the first key is a datetime...