MySQL Forums :: Partitioning :: Slow query with a table converted to range partitioned table using timestamp as key


Advanced Search

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 213 Benny Pei 01/10/2017 10:02AM
Re: Slow query with a table converted to range partitioned table using timestamp as key 132 Peter Brawley 01/10/2017 11:51AM
Re: Slow query with a table converted to range partitioned table using timestamp as key 134 Benny Pei 01/10/2017 12:06PM
Re: Slow query with a table converted to range partitioned table using timestamp as key 100 Peter Brawley 01/10/2017 12:12PM


Sorry, only registered users may post in this forum.

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.