Table Partitioning in MySQL

Table partitioning divides a single logical table into multiple physical segments. Each partition holds a subset of the data based on a partitioning expression. MySQL can prune partitions during query execution, reading only the relevant segments instead of the entire table. This dramatically improves query performance and data management on tables with millions of rows.

RANGE Partitioning

RANGE partitioning assigns rows to partitions based on whether a column value falls within a specified range. It is the most common strategy and works especially well with date-based data.

CREATE TABLE orders (
  id         INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  total      DECIMAL(10,2),
  order_date DATE NOT NULL,
  PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

LIST Partitioning

LIST partitioning assigns rows based on discrete values rather than ranges. This is useful when data naturally groups into a fixed set of categories.

CREATE TABLE regional_sales (
  id      INT NOT NULL AUTO_INCREMENT,
  region  VARCHAR(20) NOT NULL,
  amount  DECIMAL(10,2),
  PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_north  VALUES IN ('US', 'CA'),
  PARTITION p_europe VALUES IN ('UK', 'DE', 'FR'),
  PARTITION p_asia   VALUES IN ('JP', 'KR', 'IN'),
  PARTITION p_other  VALUES IN ('BR', 'AU', 'MX')
);

HASH Partitioning

HASH partitioning distributes rows evenly across a fixed number of partitions using a hash function on a column. It is useful when there is no natural range or list to partition by and you simply want to spread load.

CREATE TABLE sessions (
  id         CHAR(36) NOT NULL,
  user_id    INT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id)
)
PARTITION BY HASH(user_id)
PARTITIONS 8;

KEY Partitioning

KEY partitioning is similar to HASH but uses MySQL's internal hashing function. It can work with non-integer columns and defaults to the primary key if no column is specified.

CREATE TABLE logs (
  id         BIGINT NOT NULL AUTO_INCREMENT,
  message    TEXT,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id)
)
PARTITION BY KEY()
PARTITIONS 4;

Partition Pruning

Partition pruning is the main performance benefit. When your WHERE clause matches the partition expression, MySQL only reads the relevant partitions. Use EXPLAIN to verify that pruning is happening.

-- Only scans the p2025 partition
EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31';

Managing Partitions

Add, drop, and reorganize partitions with ALTER TABLE. Dropping a partition is nearly instant because MySQL simply removes the underlying data file rather than deleting rows one by one.

-- Add a new partition for 2026
ALTER TABLE orders
ADD PARTITION (PARTITION p2026 VALUES LESS THAN (2027));

-- Drop old data instantly
ALTER TABLE orders DROP PARTITION p2022;

-- Check partition info
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';

When to Partition

Partitioning shines on tables with tens of millions of rows or more, especially when queries commonly filter on the partition key. It also simplifies data lifecycle management -- dropping an old partition is far faster than deleting millions of rows. Avoid partitioning small tables; the overhead is not worth it when the entire table fits in memory.