A Guide to MySQL Partitioning

A technique for splitting large MySQL tables by range, list, hash, or key so that a query reads only the partitions it needs.

What partitioning is

Partitioning splits a database table into several smaller pieces to manage data more efficiently and improve performance.

Data access slows down as a table grows. With partitioning, a query scans only the partitions it needs. This improves performance.

The main benefits of partitioning

Partitioning speeds up scans by reading only the partitions a query needs. Backup, recovery, and archiving can each run per partition, which makes management easier. Independent processing of each partition also permits parallel work.

In practice, the main reasons to adopt partitioning are performance and ease of management. How much parallel processing helps depends on the workload.

MySQL partitioning methods

Range partitioning

Range partitioning splits data by continuous values such as dates and numbers.

Range partitioning distributes rows according to the value ranges of the expression given in the PARTITION BY RANGE clause. VALUES LESS THAN defines the upper bound each partition accepts.

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 (2021),
  PARTITION p1 VALUES LESS THAN (2022),
  PARTITION p2 VALUES LESS THAN (2023),
  PARTITION p3 VALUES LESS THAN (2024)
);

Advantage: a date range query scans only the matching partition Drawback: uneven data distribution concentrates rows in specific partitions

Partitioning by a function such as YEAR() requires the partitioning column to appear in every primary key and unique key. RANGE COLUMNS(order_date) compares the column value directly and avoids that restriction. It also makes partition pruning more reliable on date range queries.

List partitioning

List partitioning splits data by an explicit list of values. It suits country codes, regions, and status values. VALUES IN defines the list of values mapped to each partition.

CREATE TABLE customers (
  customer_id INT,
  customer_name VARCHAR(50),
  country_code CHAR(2)
)
PARTITION BY LIST (country_code) (
  PARTITION p_us VALUES IN ('US'),
  PARTITION p_uk VALUES IN ('UK'),
  PARTITION p_ca VALUES IN ('CA')
);

Hash partitioning

Hash partitioning uses a hash function to distribute data evenly. PARTITIONS 4 sets the number of partitions to create. MySQL splits rows across those 4 partitions by hash value.

CREATE TABLE transactions (
  transaction_id INT,
  customer_id INT,
  amount DECIMAL(10, 2)
)
PARTITION BY HASH (customer_id) PARTITIONS 4;

Advantage: even data distribution, no single partition overloaded Drawback: hard to optimize range queries

Key partitioning

Key partitioning uses MySQL's internal hash algorithm to split data by the primary key or a unique key. PARTITION BY KEY(column) names the partitioning column, and PARTITIONS n sets the partition count.

CREATE TABLE logs (
  log_id INT AUTO_INCREMENT,
  log_message VARCHAR(255)
)
PARTITION BY KEY(log_id) PARTITIONS 4;

Managing partitions

Adding a partition

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

Dropping a partition

ALTER TABLE orders DROP PARTITION p0;

Partitioning optimization tips

flowchart LR
    Q[WHERE predicate] --> P{Can MySQL derive<br/>a partition range}
    P -->|yes| S[Scan matching partitions]
    P -->|no| A[Inspect every partition]

Making use of partition pruning

Partition pruning lets a query skip partitions it does not need. Including the partitioning column in the WHERE clause lets MySQL scan only the relevant partitions.

-- Good: scans only partition p2
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';
 
-- Bad: scans every partition
SELECT * FROM orders WHERE amount > 1000;

Choosing the right tables

• Good fit: large tables with millions of rows or more
• Poor fit: small tables, where it only adds management complexity

The limits of partitioning

Summary

Partitioning cuts scan cost on large tables by reading only the partitions a query needs. Choose the method based on the data characteristics and query patterns. Use range for frequent date range queries and list for categories. Use hash or key for an even distribution.

Partition pruning works only when the WHERE clause includes the partitioning column. For tables below a few million rows, management complexity outweighs the benefit. The same applies to schemas that depend heavily on FOREIGN KEY. Assess these costs before adopting partitioning.