MySQL partitioning by week automatically

Question

I created a table to log the activity of my application. This table will log more than 2 millions record per month. So I want to use partitioning by month or week

CREATE TABLE IF NOT EXISTS `UL`.`Log` (
  `LogID` INT(20) NOT NULL AUTO_INCREMENT,
  `LogDate` DATETIME NULL,
  `AssessorName` VARCHAR(255) NULL
  PRIMARY KEY (`LogID`),
  INDEX `AssessorName` (`AssessorName`),
  INDEX `LogDate` (`LogDate`)
)
ENGINE = INNODB;

But the problem is I have to create the partitioning manually such as

PARTITION BY RANGE (EXTRACT(YEAR_MONTH FROM LogDate)) (
    PARTITION pre_2014 VALUES LESS THAN (201400),
    PARTITION jan_2014 VALUES LESS THAN (201401),
    PARTITION feb_2014 VALUES LESS THAN (201402),
....

Is the any way to create a partition by month or week automatically?

Thanks

score 3 · Accepted Answer · answered Aug 10 '15 at 05:05

Don't use YEAR_MONTH(), it is not in the list here: http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-functions.html

Instead, use TO_DAYS(LogDate) and explicit dates such as '2014-01-01'.

No, there is no "automatic" partitioning. It is not too difficult a task to write Perl (or other language) code to fetch the partition structure from the information_schema and compute what partition to add next.

What do you hope to gain from PARTITIONing? There are very few benefits. I have listed them in my blog.

Also, that link has code to deal with purging old partitions, plus how to efficiently use REORGANIZE PARTITIONS each week (or month).

MySQL partitioning by week automatically

1 Answers1