Re: Slow query with a table converted to range partitioned table using timestamp as key
Posted by:
Benny Pei
Date: January 10, 2017 12:06PM
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
Subject
Views
Written By
Posted
3276
January 10, 2017 10:02AM
1528
January 10, 2017 11:51AM
Re: Slow query with a table converted to range partitioned table using timestamp as key
1462
January 10, 2017 12:06PM
1337
January 10, 2017 12:12PM
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.