Re: How to create partition on existing table?

[Hartmut Holzgraefe]

jason zhang Wrote:
-------------------------------------------------------
> There is a table which already exists and has
> about 1 million records.
> How can I create partition on it?

you can use ALTER TABLE to add partitioning to the table,
keep in mind though that this will actually create the
new partitioned table first, then copy over all the
existing data, and finally drop the old unpartitioned
table. So this operation may take a while and will
temporarily use twice the disk space (which in the case
of InnoDB is not given back to the operating system ...)




mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
-> ;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE trb3 PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

--
Hartmut Holzgraefe, MySQL Regional Support Manager EMEA

Sun Microsystems GmbH, Sonnenallee 1, 85551 Kirchheim-Heimstetten
Amtsgericht Muenchen: HRB161028
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering