MySQL 5.1 Partitioning

I have the following example table...

mysql> CREATE TABLE part_date3
    ->      (  c1 int default NULL,
    ->  c2 varchar(30) default NULL,
    ->  c3 date default NULL) engine=myisam
    ->      partition by range (to_days(c3))
    -> (PARTITION p0 VALUES LESS THAN (to_days('1995-01-01')),
    -> PARTITION p1 VALUES LESS THAN (to_days('1996-01-01')) ,
    -> PARTITION p2 VALUES LESS THAN (to_days('1997-01-01')) ,
    -> PARTITION p3 VALUES LESS THAN (to_days('1998-01-01')) ,
    -> PARTITION p4 VALUES LESS THAN (to_days('1999-01-01')) ,
    -> PARTITION p5 VALUES LESS THAN (to_days('2000-01-01')) ,
    -> PARTITION p6 VALUES LESS THAN (to_days('2001-01-01')) ,
    -> PARTITION p7 VALUES LESS THAN (to_days('2002-01-01')) ,
    -> PARTITION p8 VALUES LESS THAN (to_days('2003-01-01')) ,
    -> PARTI开发者_运维知识库TION p9 VALUES LESS THAN (to_days('2004-01-01')) ,
    -> PARTITION p10 VALUES LESS THAN (to_days('2010-01-01')),
    -> PARTITION p11 VALUES LESS THAN MAXVALUE );
Query OK, 0 rows affected (0.00 sec)

Say this is full of data and I want to slot in a 2011 partition at p11 and then make the p12 maxvalue is there an efficient way of doing this without dumping and reloading the entire table?

To answer you exact question

I want to slot in a 2011 partition at p11 and then make the p12 maxvalue

Here is the query

ALTER TABLE part_date3 REORGANIZE PARTITION p11 INTO (
    PARTITION p11 VALUES LESS THAN (TO_DAYS('2011-01-01')),
    PARTITION p12 VALUES LESS THAN MAXVALUE
);

You need to drop the MAXVALUE partition, add the new partition and add a new MAXVALUE partition again. This is a fast operation and you would not lose data in any partition other than the MAXVALUE partition.

If you want to preserve data in MAXVALUE partion, have a look at REORGAINZE PARTITION and COALESCE PARTITION clauses of ALTER TABLE http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

I had the same problem. I dropped the MAXVALUE partition, added the new partition but did not add the MAXVALUE partition again. In my case, the app would never insert any record dataed so much in the future that it would require a MAXVALUE partition.

Partition maintenance goes very well with Events. See http://dev.mysql.com/tech-resources/articles/partitioning-event_scheduler.html

http://dev.mysql.com/doc/refman/5.1/en/partitioning-management-range-list.html suggests that you can simply add another partition via

ALTER TABLE `part_date3`
ADD PARTITION (PARTITION p12 VALUES LESS THAN (to_days('2011-01-01')));

though obviously, you should test that first.