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 instead of the whole table, which raises performance.

The main benefits of partitioning

• Better performance: reading only specific partitions speeds up scans
• Easier management: backup, recovery, and archiving can happen per partition
• Parallel processing: independent handling per partition enables 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

Splits data along 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. Using RANGE COLUMNS(order_date), which compares the column value directly, avoids that restriction and makes partition pruning behave more reliably on date range queries.

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

Uses a hash function to distribute data evenly. PARTITIONS 4 sets the number of partitions to create, and 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

Uses MySQL's internal hash algorithm to split on the primary key or a unique key. PARTITION BY KEY(column) names the splitting 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

Making use of partition pruning

Partition pruning is the behavior where a query skips 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 by analyzing the data characteristics and the query patterns. Frequent date range queries call for range, category classification calls for list, and an even spread calls for hash or key. Partition pruning works only when the WHERE clause includes the partitioning column. For tables below a few million rows, or schemas that lean heavily on FOREIGN KEY, management complexity outweighs the benefit, so weigh it before adopting.