[SOLVED] MySQL partitioning by week automatically

MySQL partitioning by week automatically

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:

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),
....

How do I create a partition by month or week automatically?

Solution

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).