Partion Types

Range
List
Columns
Hash
Key
Sub partitioning

Range Partitioning

A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

Partition By The employees table by store_id

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) 
);

or we can alternatively partition by store_id and have the other values that do no match the partions go into a catch-all partition. We use the MAXVALUE to specify all the other values.

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 
);

Partition by time intervals

We can use time intervals to partiton the table. MySQL supports the following functions when partitioning YEAR, TO_DAYS, TO_SECONDS.

CREATE TABLE members ( 
    firstname VARCHAR(25) NOT NULL, 
    lastname VARCHAR(25) NOT NULL, 
    username VARCHAR(16) NOT NULL, 
    email VARCHAR(35), 
    joined DATE NOT NULL 
) 
PARTITION BY RANGE( YEAR(joined) ) ( 
    PARTITION p0 VALUES LESS THAN (1960), 
    PARTITION p1 VALUES LESS THAN (1970), 
    PARTITION p2 VALUES LESS THAN (1980), 
    PARTITION p3 VALUES LESS THAN (1990), 
    PARTITION p4 VALUES LESS THAN MAXVALUE 
);

or we can use the date columns on the table

CREATE TABLE members ( 
    firstname VARCHAR(25) NOT NULL, 
    lastname VARCHAR(25) NOT NULL, 
    username VARCHAR(16) NOT NULL, 
    email VARCHAR(35), 
    joined DATE NOT NULL 
) 
PARTITION BY RANGE COLUMNS(joined) ( 
    PARTITION p0 VALUES LESS THAN ('1960-01-01'), 
    PARTITION p1 VALUES LESS THAN ('1970-01-01'), 
    PARTITION p2 VALUES LESS THAN ('1980-01-01'), 
    PARTITION p3 VALUES LESS THAN ('1990-01-01'), 
    PARTITION p4 VALUES LESS THAN MAXVALUE );

List Partitioning Type

Same as RANGE partitioning type. The difference is each partition is defined and selected based on the membership of column in one set of values.

CREATE TABLE test ( 
    c1 INT, 
    c2 INT
) 
PARTITION BY LIST (c1) ( 
    PARTITION p0 VALUES IN (1, 4, 7), 
    PARTITION p1 VALUES IN (2, 5, 8)
);

If you try to insert values that are not within range, you will get an error.

Column Partitioning Type

This is a variant of the List and Range partioning types where columns are used when partitioning values.

CREATE TABLE employees_by_lname (
     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 COLUMNS (lname) ( 
    PARTITION p0 VALUES LESS THAN ('g'), 
    PARTITION p1 VALUES LESS THAN ('m'), 
    PARTITION p2 VALUES LESS THAN ('t'), 
    PARTITION p3 VALUES LESS THAN (MAXVALUE) 
);

or ALTER the table and partition the table.

ALTER TABLE employees 
PARTITION BY RANGE COLUMNS (hired) ( 
    PARTITION p0 VALUES LESS THAN ('1970-01-01'), 
    PARTITION p1 VALUES LESS THAN ('1980-01-01'), 
    PARTITION p2 VALUES LESS THAN ('1990-01-01'), 
    PARTITION p3 VALUES LESS THAN ('2000-01-01'), 
    PARTITION p4 VALUES LESS THAN ('2010-01-01'), 
    PARTITION p5 VALUES LESS THAN (MAXVALUE) 
);

Hash Partitioning

Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. MySQL takes care of calculating the hash function for you.

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;

or we can pass an SQL expression that returns an integer and MySQL will calculate the hash for us.

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( YEAR(hired) ) 
PARTITIONS 4;

Key Partitioning Type

Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server.

CREATE TABLE k1 (
     id INT NOT NULL PRIMARY KEY, 
    name VARCHAR(20) 
) 
PARTITION BY KEY() 
PARTITIONS 2;

Sub Partitioning

Subpartitioning—also known as composite partitioning—is the further division of each partition in a partitioned table.

Types of Partitioning