percona mysql5.5.29 index on timestamp will not use

Asked by weiwang on 2013-04-01

I have a table defination like this:

CREATE TABLE `t_pearl_finance_detail` (
  `fin_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `partner_id` int(11) NOT NULL,
  `createtime` timestamp NULL,
  PRIMARY KEY (`fin_id`),
  KEY `createtime` (`createtime`),
  KEY `partner_id_2` (`partner_id`,`createtime`)
) ENGINE=InnoDB AUTO_INCREMENT=36023268 DEFAULT CHARSET=utf8

mysql> explain select * from t_pearl_finance_detail force index(createtime) where createtime>from_unixtime(1364486400)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_pearl_finance_detail
         type: range
possible_keys: createtime
          key: createtime
      key_len: 5
          ref: NULL
         rows: 3504312
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from t_pearl_finance_detail where createtime>from_unixtime(1364486400)\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_pearl_finance_detail
         type: ALL
possible_keys: createtime
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 9220001
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from t_pearl_finance_detail where createtime>'2013-03-30'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_pearl_finance_detail
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8732399
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from t_pearl_finance_detail force index(createtime) where createtime>'2013-03-30'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_pearl_finance_detail
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8732539
        Extra: Using where
1 row in set (0.00 sec)

I wonder why above sql will not use the 'createtime' index?

Question information

Language:
English Edit question
Status:
Solved
For:
Percona Server Edit question
Assignee:
No assignee Edit question
Solved by:
Alexey Kopytov
Solved:
2013-04-12
Last query:
2013-04-12
Last reply:
2013-04-12
Best Alexey Kopytov (akopytov) said : #1

This may be http://bugs.mysql.com/bug.php?id=64998. Can you check if you case is repeatable with collation_connection=utf8_general_ci ?

weiwang (weiwang) said : #2

With collation_connection=utf8_general_ci works fine. Thanks.