MySQL 5.1 Partitioning

Question

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')) ,
    -> PARTITION 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?

Answer 1

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