MySQL partitions

Have a short look at mysql partitioning.

Useful to handle large databases, you can even store partitions on different devices.
I want to partition data on a (presumed to be) large table by timestamp, more precise I want a partition for each month. And I want the system to be able to detect when to add new partitions on its own (upcoming blogpost for this).

mysql partitions

Looks like my MySQL version (5.1.54-1ubuntu4 ) does not allow to use to_days for partitioning, but for MySQL before 5.1.43 the only way to partition by date seems to be something like this:

CREATE TABLE example (
id INT NOT NULL AUTO_INCREMENT,
some_info VARCHAR(30),
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY (id)
)
PARTITION BY RANGE ( to_days( created ) ) (
PARTITION p0 VALUES LESS THAN ( to_days( ‘2011-05-01’ ) ),
PARTITION p1 VALUES LESS THAN ( to_days( ‘2011-06-01’ ) ),
PARTITION p2 VALUES LESS THAN ( to_days( ‘2011-07-01’ ) ),
PARTITION p3 VALUES LESS THAN ( to_days( ‘2011-08-01’ ) )
);

OK, but my version is newer (and indignantly complains: ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed) so let’s try with

DROP TABLE IF EXISTS example;

CREATE TABLE example (
id        INT NOT NULL AUTO_INCREMENT,
some_info VARCHAR(30) DEFAULT ”,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY (id)
)
PARTITION BY RANGE ( UNIX_TIMESTAMP( created ) ) (
PARTITION p_05_2011 VALUES LESS THAN ( UNIX_TIMESTAMP( ‘2011-05-01 00:00:00’ ) ),
PARTITION p_06_2011 VALUES LESS THAN ( UNIX_TIMESTAMP( ‘2011-06-01 00:00:00’ ) ),
PARTITION p_07_2011 VALUES LESS THAN ( UNIX_TIMESTAMP( ‘2011-07-01 00:00:00’ ) ),
PARTITION p_08_2011 VALUES LESS THAN ( UNIX_TIMESTAMP( ‘2011-08-01 00:00:00’ ) ),
PARTITION last_part VALUES LESS THAN MAXVALUE
);

INSERT INTO example ( some_info, created ) VALUES ( ‘blafoo’, ‘2011-05-04 10:00:00’ );
INSERT INTO example ( some_info, created ) VALUES ( ‘blafoo’, ‘2011-06-04 11:00:00’ );
INSERT INTO example ( some_info, created ) VALUES ( ‘blafoo’, ‘2011-07-04 12:00:00’ );
INSERT INTO example ( some_info, created ) VALUES ( ‘blafoo’, ‘2011-08-04 13:00:00’ );

You can easily drop partitions (and all data that’s in there) by calling for example
ALTER TABLE example DROP PARTITION last_part;

Note: if you want to insert data into a partitioned table where you don’t have a partition fitting for this data, you get an error. That’s why you should always have a last partition with ‘VALUES LESS THAN MAXVALUE’  and no gaps in between.
no partition available

Good to know: you cannot have a primary key for id if you use partitions.

Adding new range partitions is only possible if you append them to the end of the table. “Ranges should be contiguous but not overlapping, and are defined using the VALUES LESS THAN operator.”

insert partition

insert partition

insert partition

This entry was posted in mysql. Bookmark the permalink.

Leave a Reply