MySQL Partitioning by Year and Week

Question

I want to use mysql partition tables to partition a table into YEAR and the WEEK number. I know exactly how to do this with mysql merge tables but partition tables are different. Can someone please help with the following table schema?

CREATE TABLE `tableName` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `dateandtime` datetime NOT NULL,
  `othervalue` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Also does it have to be in a certain engine?

And if I store the dateandtime as a int(10) timestamp how would I do it?

CREATE TABLE `tableName` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `dateandtime` int(10) NOT NULL,
  `othervalue` int(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

Answer 1

MySQL 5.1 cannot do partition by date, so you must you workaround... Usually you partition by function TO_DAYS(dateandtime), for example like this:

CREATE TABLE tbl (
... ) ENGINE=InnoDB
PARTITION BY RANGE (to_days(dateandtime)) (
 PARTITION pNULL VALUES LESS THAN (0) ENGINE = InnoDB,
 PARTITION p20111218 VALUES LESS THAN (TO_DAYS('2011-12-18')) ENGINE = InnoDB,
 PARTITION p20111225 VALUES LESS THAN (TO_DAYS('2011-12-25')) ENGINE = InnoDB,
 PARTITION pNew VALUES LESS THAN MAXVALUE ENGINE = InnoDB
)

I defined here 4 partitions - the first is just for sake of completeness, so that insert of date in future won't fail. (You can't INSERT a value for which a partition does not exist.) The first partition is for performance - NULL values will be petentionally stored there. The middle 2 partitions are actually being used, each keeping one week of data.

You can drop old partition (this is very fast compared to just DELETEing old rows) using ALTER TABLE tbl DROP PARTITION xyz. You can add new partitions by splitting the last partition:

ALTER TABLE tbl REORGANIZE PARTITION pNew INTO (
  PARTITION p20120115 VALUES LESS THAN (TO_DAYS('2012-01-16')),
  ...
  PARTITION pNew VALUES LESS THAN (MAXVALUE)
);