percona mysql5.5.29 index on timestamp will not use

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?

Seems that you are hitting this bug:

http://bugs.mysql.com/bug.php?id=64998

Can you try to run the same query on PS with collation_connection=utf8_general_ci and collation_connection=utf8_unicode_ci ?

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.