Hi,
The basic support for Partitioning is now a part of MySQL 5.1 and will be available as soon as the
first 5.1 version gets public (no fixed plans for this yet).
I am currently working out the management parts and the following commands will be available
to start with.
1) When ALTER TABLE is issued a completely new partitioning can be imposed on the table. This
can be applied also on any table not using partitioning previously. ALTER TABLE can also be used
to move a partitioned table into a non-partitioned table so there is complete flexibility here.
2) ALTER TABLE ADD PARTITION (definition of a set of PARTITIONS) or
ALTER TABLE ADD PARTITION PARTITIONS number (only for HASH partitions)
For RANGE and LIST partitions these commands will be simply adding new empty partitions,
for HASH partitions data will be reorganised, either the full table or a part of it (when using
LINEAR HASH/KEY partitions, see post on HASH partitioning for more info). RANGE partitions
must always be added at the end since ranges are increasing.
3) ALTER TABLE DROP PARTITION list of partition names
A list of names of partitions to drop is provided, the table will remain but all data in those
partitions will be thrown away. For RANGE partitioning this will also change ranges on the
remaining partitions. The most common usage of this command is to drop the first partition
of a RANGE partition.
The command can only be applied to RANGE and LIST partitions.
4) ALTER TABLE COALESCE PARTITON number
This command will decrease the number of partitions in a HASH partitioned table and also
reorganising the table. The partitions removed are the last partitions and data will move into the
remaining partitions.
5) ALTER TABLE REORGANISE PARTITION list of partition names (list of partition definitions)
This command can be used to change a set of partitions and not all of them. It can be used to
merge a set of partitions in which case the list of partition names has several partition given
and the new partition is only one to receive all the data from those partitions. It can also be
used to split partitions by listing one partition and the new partitions is a set of partitions. It can
also be used to change ranges for a subset of the partitions and changing the VALUES IN for
a set of partitions. Essentially a very powerful change tool that has the advantage that it can
work on any subset of the partitions whereas a full ALTER TABLE always reorganises the full
table.
On the TODO list for later is also commands to move a partition into its own table and vice versa.
If the table had subpartitions from the start this doesn't change with the commands above, the new
partitions added or reorganised also need to have the same subpartitioning. If the subpartitioning
is required to change then a full ALTER TABLE is needed currently.
If you have ideas or requirements on how these changes should be adapted to better fit your
needs please post them here so that I can adapt the implementation as it proceeds forward here.
Rgrds Mikael
Mikael Ronstrom
Senior Software Architect, MySQL AB
My blog:
http://mikaelronstrom.blogspot.com