MySQL Forums
Forum List  »  Partitioning

Slow query with a table converted to range partitioned table using timestamp as key
Posted by: Benny Pei
Date: January 10, 2017 10:02AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query with a table converted to range partitioned table using timestamp as key
562
January 10, 2017 10:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.