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

[Benny Pei]

Thank you. I looked at a few posts down and it has a similar problem as mine. I have a similar usage case. I wasn't looking to improve performance but rather to easily manage expired data. (instead of running delete for hours, I could simply drop the partition within a few seconds).

The data retention is 3 months so I need to maintain six to seven partitions. If the number of partitions would cause the query to run slower, would reduce the number of partitions to "speed things up"? Since my retention is 3 months, I could change each partition to contain each quarter of the data. Q1 to Q4. I would have about two to three partitions to scan instead?

Would that work?

I am currently making three months of data into one partitions and let me see how that goes.


The unpartitioned table DDL is like this. I am not adding columns. The table structure is the same.

CREATE TABLE `foo` (
`col1` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
`col2` varchar(45) NOT NULL,
`col3` timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`col4` varchar(50) NOT NULL,
`col5` varchar(10) NOT NULL,
`col6` varchar(1000) NOT NULL,
`col7` varchar(45) DEFAULT NULL,
UNIQUE KEY `idx1` (`col1`,col3),
KEY `idx2` (`col2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

benny