Slow query with a table converted to range partitioned table using timestamp as key

[Benny Pei]

Hi all,

I am running mysql 5.5. I have a 180GB table that I have converted to a range partitioned table using the timestamp column as the partition key. Here is the DDL.

create table foo (
col1 bigint unsigned not null auto_increment,
col2 timestamp not null default current_timestamp on update current_timestamp,
col3 varchar(2),
unique key (col1,col2),
) partition by range ( unix_timestamp(col2))
( partition pa201509 values less than (1443672000),
partition pa201510 VALUES LESS THAN (1446350400),
...
partition pa201608 VALUES LESS THAN (1472702400))

A typical select count(*) on the original un-partitioend table took about 4-5 minutes. But once the partition table is setup, the select count(*) is taking more than an hour and still running. I am not sure why and there was nothing running on the database. I start to wonder whether it is typical to experience such slowness with timestamp column as partition key? Is there anything I should do to improve it? Like convert the timestamp to datetime instead? or any thing?

thanks,

benny