Relationship and restrictions between MySQL partition and key

MySQL has introduced the partition function since 5.1, which is indeed a very good feature and can help solve many problems. During partition creation, note that all columns involved in partition must appear in all unique indexes.

For example, a table has two indexes: Unique index1 (col1, col2) and unique index2 (col1). When partition is created, by range (col1) is created successfully. However, by range (col2 ), creation fails. Because both indexes contain col1, but only one index contains col2.

The table's primary key is also a unique key and must meet the preceding constraints.

Note that if the table structure does not contain any unique key, partitions can be created on any column.

 

For more details, see the MySQL Manual: http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html