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?