# percona mysql5.5.29 index on timestamp will not use

**URL:** <https://forums.percona.com/t/percona-mysql5-5-29-index-on-timestamp-will-not-use/2029>\
**Category:** Other MySQL® Questions\
**Created:** [April 1, 2013, 10:39pm UTC](https://forums.percona.com/t/percona-mysql5-5-29-index-on-timestamp-will-not-use/2029 "2013-04-01T22:39:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![weiwang](https://avatars.discourse-cdn.com/v4/letter/w/e5b9ba/32.png) [@weiwang](https://forums.percona.com/u/weiwang)\
**Post date:** [April 1, 2013, 10:39pm UTC](https://forums.percona.com/t/percona-mysql5-5-29-index-on-timestamp-will-not-use/2029/1 "2013-04-01T22:39:37Z")

</div>

I have a table defination like this:

CREATE TABLE `t_pearl_finance_detail_test` (  
`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

on percona mysql5.5.29

mysql\> explain select \* from t\_pearl\_finance\_detail\_test where createtime=‘2013-03-28’;  
±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±--------±------------ +  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±--------±------------ +  
| 1 | SIMPLE | t\_pearl\_finance\_detail\_test | ALL | NULL | NULL | NULL | NULL | 1254215 | Using where |  
±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±--------±------------ +

Execution results

mysql\> select \* from t\_pearl\_finance\_detail\_test where createtime=‘2013-03-28’;  
Empty set (2.36 sec)

on mysql5.1.48

mysql\> explain select \* from t\_pearl\_finance\_detail\_test where createtime=‘2013-03-28’;  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+  
| 1 | SIMPLE | t\_pearl\_finance\_detail\_test | ref | createtime | createtime | 5 | const | 1 | Using where |  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+

Execution results

mysql\> select \* from t\_pearl\_finance\_detail\_test where createtime=‘2013-03-28’;  
Empty set (0.00 sec)

on percona mysql5.5.29 force index dot not work

mysql\> explain select \* from t\_pearl\_finance\_detail\_test force index(createtime) where createtime=‘2013-03-28’;  
±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±--------±------------ +  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±--------±------------ +  
| 1 | SIMPLE | t\_pearl\_finance\_detail\_test | ALL | NULL | NULL | NULL | NULL | 1254215 | Using where |  
±—±------------±----------------------------±-----±-- ------------±-----±--------±-----±--------±------------ +

on percona mysql5.5.29 force index with from\_unixtime work fine

mysql\> select unix\_timestamp(‘2013-03-28’);  
±-----------------------------+  
| unix\_timestamp(‘2013-03-28’) |  
±-----------------------------+  
| 1364400000 |  
±-----------------------------+

mysql\> explain select \* from t\_pearl\_finance\_detail\_test force index(createtime) where createtime=from\_unixtime(1364400000);  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+  
| 1 | SIMPLE | t\_pearl\_finance\_detail\_test | ref | createtime | createtime | 5 | const | 1 | Using where |  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+

Execution results

mysql\> select \* from t\_pearl\_finance\_detail\_test force index(createtime) where createtime=from\_unixtime(1364400000);  
Empty set (0.00 sec)

on percona mysql5.5.29 with from\_unixtime work fine

mysql\> explain select \* from t\_pearl\_finance\_detail\_test where createtime=from\_unixtime(1364400000);  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+  
| 1 | SIMPLE | t\_pearl\_finance\_detail\_test | ref | createtime | createtime | 5 | const | 1 | Using where |  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+

my question is :  
1.why on percona mysql5.5.29 force index do not work?  
2.I think the explain result on mysql5.1 is good, why percona should use from\_unixtime?

---

<div class="post-metadata">

**Author:** ![miguelangelnieto](https://avatars.discourse-cdn.com/v4/letter/m/b9e5f3/32.png) [@miguelangelnieto](https://forums.percona.com/u/miguelangelnieto)\
**Post date:** [April 2, 2013, 4:06am UTC](https://forums.percona.com/t/percona-mysql5-5-29-index-on-timestamp-will-not-use/2029/2 "2013-04-02T04:06:17Z")

</div>

Seems that you are hitting this bug:

[URL][MySQL Bugs: #64998: Index on TIMESTAMP not used depending on collation\_connection](http://bugs.mysql.com/bug.php?id=64998%5B/URL%5D)

Can you try to run the same query on PS with collation\_connection=utf8\_general\_ci and collation\_connection=utf8\_unicode\_ci ?

---

<div class="post-metadata">

**Author:** ![weiwang](https://avatars.discourse-cdn.com/v4/letter/w/e5b9ba/32.png) [@weiwang](https://forums.percona.com/u/weiwang)\
**Post date:** [April 2, 2013, 5:02am UTC](https://forums.percona.com/t/percona-mysql5-5-29-index-on-timestamp-will-not-use/2029/3 "2013-04-02T05:02:03Z")

</div>

The default collation\_connection like this:

mysql\> show variables like “%collation\_connection%”;  
±---------------------±----------------+  
| Variable\_name | Value |  
±---------------------±----------------+  
| collation\_connection | utf8\_unicode\_ci |  
±---------------------±----------------+

mysql\> set session collation\_connection=‘utf8\_general\_ci’;  
Query OK, 0 rows affected (0.04 sec)

mysql\> show variables like “%collation\_connection%”;  
±---------------------±----------------+  
| Variable\_name | Value |  
±---------------------±----------------+  
| collation\_connection | utf8\_general\_ci |  
±---------------------±----------------+

mysql\> explain select \* from t\_pearl\_finance\_detail\_test where createtime=‘2013-03-28’;  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+  
| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+  
| 1 | SIMPLE | t\_pearl\_finance\_detail\_test | ref | createtime | createtime | 5 | const | 1 | Using where |  
±—±------------±----------------------------±-----±-- ------------±-----------±--------±------±-----±-------- ----+

On PS with collation\_connection=utf8\_general\_ci work fine, just like the bug you list above.

The solution is:  
1.set collation\_connection=utf8\_general\_ci  
2.from\_unixtime

and others?

thanks for your reply.