CODEWITHSUNDEEP
mysqlsqldatabasemysql-workbench
Rob
Rob

Reputation: 143

Partition very large table by date - MySQL

I would like to create a table with a partition on a date format column in MySQL and would like the partition to be updated each time an additional date is added (every day).

What is the best approach for this and what is the code used for this?

Thanks in advance

Upvotes: 4

Views: 4874

Answers (1)

IGGt
IGGt

Reputation: 2769

If we take a simple example:

CREATE TABLE `test`.`NewTable` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `created` DATETIME NULL,
 PRIMARY KEY (`id`,`created`)
);

ALTER TABLE `test`.`NewTable`
PARTITION BY RANGE (TO_DAYS(`Created`)) (
 PARTITION `20150531` VALUES LESS THAN (TO_DAYS('2015-06-01 00:00:00')),
 PARTITION `20150601` VALUES LESS THAN (TO_DAYS('2015-06-02 00:00:00')),
 PARTITION `20150602` VALUES LESS THAN MAXVALUE
);

This gives you a table with three partitions:

20150531 = all [OLD] data before 1st June
20150601 = all data from 1st June
20150602 = all [FUTURE] data after 1st June (or with an incorrect future timestamp)

At the start of 2nd June, you want to create a new partition which will hold all of the data for 2nd June. To do this you need to split the 20150602 partition into two partitions.

20150602 for all data from the 2nd June
20150603 for all [FUTURE] data after the 2nd June (or with an incorrect future timestamp)

This is done with:

ALTER TABLE `test`.`NewTable` 
REORGANIZE PARTITION 20150602 INTO (
 PARTITION 20150602 VALUES LESS THAN (TO_DAYS('2015-06-03 00:00:00')),  
 PARTITION 20150603 VALUES LESS THAN MAXVALUE
);

In this way you can then use:

SELECT MAX(PARTITION_NAME) FROM INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = 'test' 
AND TABLE_NAME = 'NewTable';

to get your FUTURE partition, and from there it should be straightforward to work out what the next partition need to be.

The Event I use for this:

DROP EVENT AddNewPartition_daily;
DELIMITER $$
CREATE EVENT AddNewPartition_daily
ON SCHEDULE
EVERY 1 DAY STARTS CONCAT(DATE_SUB(CURDATE(), INTERVAL -1 DAY),' 00:00:01')
ON COMPLETION PRESERVE
COMMENT 'Adds a New Partition to the Top, and Removes the Oldest Partition From NewTable'
DO
BEGIN
SET @MinP := (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 9 DAY),'%Y%m%d'));
SET @MaxP := (SELECT MAX(PARTITION_NAME) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'NewTable');

SET @Today := (SELECT CONCAT(CURDATE(),' 00:00:00'));
SET @Tomorrow := (SELECT CONCAT(DATE_SUB(CURDATE(), INTERVAL -1 DAY),' 00:00:00'));

SET @TodP := (SELECT DATE_FORMAT(CURDATE(),'%Y%m%d'));
SET @TomP := (SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL -1 DAY),'%Y%m%d'));

#ADD NEW PARTITION TO TOP
SET  @SQL1 := CONCAT('
ALTER TABLE test.NewTable
REORGANIZE PARTITION `',@TodP,'` INTO
(
    PARTITION `',@TodP,'` VALUES LESS THAN (TO_DAYS(\'',@Tomorrow,'\')),
    PARTITION `',@TomP,'` VALUES LESS THAN MAXVALUE
)');

     PREPARE stmt FROM @SQL1;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt;

#REMOVE OLDEST PARTITION
SET @SQL2 := CONCAT('ALTER TABLE test.NewTable DROP PARTITION `',@MinP,'`');  

PREPARE stmt FROM @SQL2;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END $$
DELIMITER ;

You should note, that if you are just continually adding partitions without removing them, there will eventually be a performance hit

Upvotes: 10

Related Questions