Range partitioning in MySQL 5.1

There are 5 types of table partitions in mysql: range, list, hash, key and composite (or subpartitioning), i think the most commonly used type in the database world (at least in oracle) could be range partition, in this way a expression is evaluated and according to it different partitions are created with ranges of rows. The most common method to partition by range is using dates (by years,months,days) to group large amounts of rows from a table, mysql does not support partition by date type directly (oracle does), instead a function like year() or month() should be used to get an integer value.


Playing with this partition type, first i tried to create a table with a primary key on the id column and the partition column using insert_date, but MySQL generates an error if the column insert_date is not part of the primary key:

mysql> CREATE TABLE range_partition_tab (
 ->  id numeric primary key,
 ->  data varchar(20),
 ->  insert_date date
 -> )
 -> PARTITION BY RANGE ( year (insert_date) ) (
 ->  PARTITION p1 VALUES LESS THAN (2005),
 ->  PARTITION p2 VALUES LESS THAN (2006),
 ->  PARTITION P3 VALUES LESS THAN MAXVALUE
 -> );
ERROR 1482 (HY000): A PRIMARY KEY need to include all fields in the partition function

But after i removed the primary key clause , it created the table.

mysql> CREATE TABLE range_partition_tab (
 ->  id numeric ,
 ->  data varchar(20),
 ->  insert_date date
 -> )
 -> PARTITION BY RANGE ( year (insert_date) ) (
 ->  PARTITION p1 VALUES LESS THAN (2005),
 ->  PARTITION p2 VALUES LESS THAN (2006),
 ->  PARTITION P3 VALUES LESS THAN MAXVALUE
 -> );
Query OK, 0 rows affected (0.08 sec)

I tried to add a new partition:

mysql>  alter table range_partition_tab  add partition (partition p4 values less than (2007));
ERROR 1472 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

But p3 holds all the rows that match range for p4, then since none value is greater than MAXVALUE a new partition can't be added over that value.

New partitions can be added for example by splitting the low end partition (p1) with the REORGANIZE PARTITION clause:

 mysql> alter table range_partition_tab  reorganize partition p1 into (    partition s0 values less than (2004),    partition s1 values less than (2005) );
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

The new partition must start from the value of p1 wich is 2005 to cover the same range, otherwise MySQL generates an error:

mysql> alter table range_partition_tab  reorganize partition P3 into (    partition s0 values less than (2003),    partition s1 values less than (2004) );
ERROR 1499 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

to drop a partition:

mysql> alter table range_partition_tab drop partition p3;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0