Creating Partitioned Tables

To create a partitioned table in MySQL we need to define the partitioning scheme. The MySQL supports several types of the partitioning methods:

1. Range Partitioning: Data is divided based on a range of values.
2. List Partitioning: Data is divided based on a list of the values.
3. Hash Partitioning: Data is divided based on the hash function.
4. Key Partitioning: Data is divided based on the key.

Range Partitioning

Range partitioning divides data into partitions based on the specified range of values. This is useful for partitioning data based on the dates or numeric ranges.

CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2019),
    PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN (2022)
);

In this example, the orders table is partitioned by the year with each partition containing orders from the specific year.

List Partitioning

The List partitioning divides data based on a predefined list of the values. This is useful when partitioning data by categories or specific values.

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(50),
    department VARCHAR(50)
)
PARTITION BY LIST COLUMNS(department) (
    PARTITION p0 VALUES IN ('HR', 'Finance'),
    PARTITION p1 VALUES IN ('Engineering', 'Sales'),
    PARTITION p2 VALUES IN ('Marketing', 'Support')
);

In this example, the employees table is partitioned by the department with each partition containing employees from the specific departments.

Hash Partitioning

The Hash partitioning divides data based on the hash function which distributes data evenly across partitions. This is useful for ensuring an even distribution of the data.

CREATE TABLE users (
    user_id INT,
    user_name VARCHAR(50),
    email VARCHAR(50)
)
PARTITION BY HASH(user_id) PARTITIONS 4;

In this example, the user’s table is partitioned using the hash function on the user_id column distributing data across the four partitions.

Key Partitioning

The Key partitioning is similar to the hash partitioning but uses MySQL’s internal function for the partitioning.

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2)
)
PARTITION BY KEY(product_id) PARTITIONS 4;

In this example, the products table is partitioned by the product_id column using MySQL’s internal key function.

Managing Partitions

Managing partitions involves performing operations such as adding, dropping, merging, and splitting partitions. These operations help maintain the performance and manageability of the partitioned tables.

Adding a Partition

To add a new partition to an existing table:

ALTER TABLE orders ADD PARTITION (   
 PARTITION p4 VALUES LESS THAN (2023)
);

Dropping a Partition

To drop a partition from the existing table:

ALTER TABLE orders DROP PARTITION p0;

Merging Partitions

To merge two partitions into one:

ALTER TABLE orders REORGANIZE PARTITION p1, p2 INTO (    
PARTITION p1_2 VALUES LESS THAN (2021)
);

Splitting Partitions

To split a partition into two:

ALTER TABLE orders REORGANIZE PARTITION p3 INTO (    
PARTITION p3_1 VALUES LESS THAN (2022),    
PARTITION p3_2 VALUES LESS THAN (2023)
);

The MySQL table partitioning feature divides large tables into smaller, more manageable partitions. Each partition can be thought of as a separate sub-table with its own storage engine, indexes, and data. Partitioning is particularly useful for improving query performance reducing the index size and enhancing data management in scenarios where tables grow extremely large.

In this article, we’ll explore the fundamentals of MySQL table partitioning its benefits supported partitioning types, and how to implement partitioning in MySQL databases.