Partitioning Methods

MySQL 5.1.3 and higher allows you to divide table and index data into smaller parts - partitions and subpartitions that can be stored and managed individually.

Note

To check whether the current version of MySQL server supports partitioning, go to Database -> Sever Variables and check the value of the system variable have_partitioning. It must be YES.

MySQL provides you a handy interface for creating partitioned tables and manipulating partitions and subpartitions.

Note

MySQL does not support partitioning of MERGE and CSV tables. Partitioning of BLACKHOLE tables is supported starting from version 5.1.6.

MySQL provides you several partitioning methods. They are range, range columns, hash, key, linear hash, linear key, list, list columns partitioning, and composite partitioning.

Range partitioning provides mapping rows to partitions based on ranges of partitioning expression value.

Range Columns partitioning is similar to range partitioning, but enables you to define partitions using ranges based on multiple column values. In addition, you can define the ranges using columns of types other than integer types.

List partitioning provides mapping rows to partitions based on lists of discrete values set for each partition.

List Columns partitioning is similar to list partitioning, but enables the use of multiple columns as partition keys, and for columns of data types other than integer types to be used as partitioning columns; you can use string types, DATE, and DATETIME columns.

Note

Range Columns and List Columns partitioning are supported starting from MySQL 5.5.0.

Hash partitioning provides mapping rows to partitions based on a value of the specified hash function of partitioning expression value.

Key partitioning provides mapping rows to partitions based on MySQL hash function of the specified partitioning key.

Linear hash and linear key methods differ from regular hash and key partitioning in that linear hashing utilizes a linear powers-of-two algorithm whereas regular hashing employs the modulus of the hashing function’s value.

If the table to be partitioned has a UNIQUE or PRIMARY key, then any columns supplied as arguments to the HASH user function or to the KEY’s column_list must be part of that key, except for the NDBCluster tables.

When the composite partitioning method is applied, a table is divided into partitions with the help of the range or list partitioning method and partitions can be further divided into subpartitions with the hash or key subpartitioning method. Each partition must have the same number of subpartitions.

The partitioning expression can be any expression that returns non-constant non-random integer or integer subtype value. The partitioning key is a set of one or more table columns that determines the partition.

MySQL allows you to alter table partitioning for the existing tables. On MySQL 5.1.6 and higher it is possible to partition a non-partitioned table. With dbForge Studio you can add or remove partitions, change range or list partition values, and even add and remove the whole partitioning.

Note

Hash and Key partitions cannot be removed.