The following section outlines the various types of partitioning techniques that are supported by MySQL.
RANGE Partitioning
| Features |
| 1.Tables are partitioned based on the column values falling within a given range. |
| 2. Ranges should be contiguous but not overlapping. |
The following example illustrates the range-partitioning technique. First, we create an employees table, as shown below:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
);
Among the list of columns, store_id can be used to partition the entire table based on the values available with it, as shown below:
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN (21)
);
It is important to note that each partition in defined in order from lowest to highest.
For accommodating rows with some higher and/or greater values in the partitions, a clause named VALUE LESS THAN is used in the create table.. statement, as show below.
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT NOT NULL,
store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
PARTITION p0 VALUES LESS THAN (6),
PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN
MAXVALUE
);
MAXVALUE represents an integer value that is always greater than the largest possible value that is available.
LIST Partitioning
| Features |
| 1. Very analogous to range partitioning. |
| 2. Partition is selected based on columns matching a set of discrete values. |
| 3. Each partition should be explicitly defined. |
| 4. Partitions do not need to be declared in any order. |
The following example demonstrates how to list partition a table based on a column value.
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY LIST(store_id) (
PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
);
Deleting data from a partition works more efficiently than deleting the data directly from the table.
HASH Partitioning
| Features |
| 1. Ensures an even distribution of data among partitions. |
| value is to be stored. |
The following example demonstrates how to hash partition a table based on a column.
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
If you do not include the PARTITIONS clause, the number of partitions defaults to 1.
KEY Partitioning
| Features |
| 1. Conceptually and syntactically, it is analogous to HASH partitioning. |
| 2. The requisite hashing function is supplied by MySQL server. |
| 3. Partitioning key must consist of table's primary key. |
CREATE TABLE k1 (
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20)
)
PARTITION BY KEY()
PARTITIONS 2;
If there is no primary key available with the table but there is a unique key, then the unique key can be used as a partitioning key, as shown below.
CREATE TABLE k1 (
id INT NOT NULL,
name VARCHAR(20),
UNIQUE KEY (id)
)
PARTITION BY KEY()
PARTITIONS 2;
MySQL Optimizer typically contains the set of routines that decide what execution path the database server should take for executing the queries.
