MySQL Partition Types

In MySQL, partitioning is done to split or partition rows of a table in different locations, but still, it is considered a single table. Partitioning distributes portions of the table across the file system based on the rules we have set as our initial requirement.

Here are some of the partition types:

1. Range Partitioning
2. List Partitioning
3. Hash Partitioning
4. Key Partitioning
5. Subpartitioning

Range Partitioning

• Range Partitioning allows us to partition rows of a table based on column values with a specified range.
• It uses less than the operator, and the provided range is in contiguous form but does not overlap each other.
• We will be using the Store_Sales table here to partition table rows based on the bill_date column in four ways.

CREATE TABLE Store_Sales ( cust_id INT NOT NULL, name VARCHAR(40),   
store_id VARCHAR(20) NOT NULL, bill_no INT NOT NULL,   
bill_date DATE PRIMARY KEY NOT NULL, amount DECIMAL(8,2) NOT NULL)   
PARTITION BY RANGE (year(bill_date))(   
PARTITION p0 VALUES LESS THAN (2016),   
PARTITION p1 VALUES LESS THAN (2017),   
PARTITION p2 VALUES LESS THAN (2018),   
PARTITION p3 VALUES LESS THAN (2020));  

• We can see here that the partitions are created perfectly.

List Partitioning

• Same as range partitioning, partitioning is defined and based on columns matching one of a set of discrete value lists rather than a continuous range of values.
• Performed by 'partition by list(exp).'
• Let us imagine we have different smartphone shops with varying brands of mobiles. Let us implement list partitioning in this.

CREATE TABLE Showrooms (   
    cust_name VARCHAR(40),   
    bill_no VARCHAR(20) NOT NULL,   
    phone_quantity INT PRIMARY KEY NOT NULL,   
    bill_date DATE NOT NULL,   
    amount DECIMAL(8,2) NOT NULL  
)  
PARTITION BY LIST(phone_quantity) (   
PARTITION pSmarthub VALUES IN (101, 103, 105),   
PARTITION pPhonecity VALUES IN (102, 104, 106),   
PARTITION pBlucity VALUES IN (107, 109, 111),   
PARTITION picity VALUES IN (108, 110, 112)); 

• Showrooms table only accepts phone_quantity number from out of this partition list and won't allow otherwise.

Hash Partitioning

• It splits the table as the user's defined expression, meaning hash partitioning is used to distribute data based on a predefined number of partitions.
• They are mainly used to distribute data evenly.

CREATE TABLE Showrooms (   
    cust_name VARCHAR(40),   
    bill_no VARCHAR(20) NOT NULL,   
    phone_quantity INT PRIMARY KEY NOT NULL,   
    bill_date DATE NOT NULL,   
    amount DECIMAL(8,2) NOT NULL  
)  
PARTITION BY HASH(phone_quantity) 
partition 3;

Key Partitioning

• Same as a hash partition, but here we will be using the key-defined column rather than a user-defined expression in like hash partition.
• If there is a primary key in a table key partition, consider it and use it.

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

Subpartitioning

• A composite partitioning where splits each partition into a partition table.

CREATE TABLE detail (   
    id INT NOT NULL PRIMARY KEY,  
    name VARCHAR(40),  
    purchased DATE  
)  
 PARTITION BY RANGE( YEAR(purchased) )  
    SUBPARTITION BY HASH( TO_DAYS(purchased) )  
    SUBPARTITIONS 2 (  
        PARTITION p0 VALUES LESS THAN (2015),  
        PARTITION p1 VALUES LESS THAN (2020),  
        PARTITION p2 VALUES LESS THAN MAXVALUE  
    );