Partitioning — MySQL

MySQL Table Partitioning

Partitioning divides a single logical table into multiple physical segments stored separately on disk. From the application perspective the table looks and behaves identically, but MySQL can skip entire partitions during queries — a technique called partition pruning — dramatically improving performance on large tables.

When Partitioning Actually Helps

Partitioning is not a silver bullet. It provides real benefit in specific scenarios:

Tables with tens or hundreds of millions of rows where queries always filter on the partition key.
Data lifecycle management — purge old data by dropping a partition instead of running a slow DELETE on millions of rows.
Time-series data queried by date ranges (logs, events, metrics, orders by year/month).
Parallel I/O — partitions can reside on different disks, spreading I/O load.

Warning

For tables with fewer than a few million rows, partitioning adds overhead without benefit. A well-designed composite index on a non-partitioned table is almost always faster. Always benchmark before adding partitioning.

RANGE Partitioning

Each partition holds rows whose partitioning column value falls within a defined range. This is the most common type, especially for date-based data.

SQL

-- Partition an orders table by year
CREATE TABLE orders (
  order_id    INT          NOT NULL,
  customer_id INT          NOT NULL,
  order_date  DATE         NOT NULL,
  amount      DECIMAL(10,2),
  PRIMARY KEY (order_id, order_date)   -- partitioning column must be in PK
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax  VALUES LESS THAN MAXVALUE   -- catch-all for future rows
);

Use RANGE COLUMNS to partition on DATE columns directly without wrapping in a function:

SQL

CREATE TABLE events (
  id         BIGINT NOT NULL AUTO_INCREMENT,
  event_date DATE   NOT NULL,
  payload    JSON,
  PRIMARY KEY (id, event_date)
)
PARTITION BY RANGE COLUMNS (event_date) (
  PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
  PARTITION p2024 VALUES LESS THAN ('2025-01-01'),
  PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);

Note

RANGE COLUMNS supports DATE columns directly and can also partition on multiple columns simultaneously, enabling fine-grained ranges like year+month partitioning without a function wrapper.

LIST Partitioning

Each partition holds rows whose column value appears in an explicit list. Useful for categorical data like region codes or status values.

SQL

CREATE TABLE sales (
  id       INT         NOT NULL,
  region   VARCHAR(20) NOT NULL,
  amount   DECIMAL(10,2),
  PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS (region) (
  PARTITION p_north VALUES IN ('NORTH', 'NORTHEAST', 'NORTHWEST'),
  PARTITION p_south VALUES IN ('SOUTH', 'SOUTHEAST', 'SOUTHWEST'),
  PARTITION p_east  VALUES IN ('EAST'),
  PARTITION p_west  VALUES IN ('WEST')
);

Warning

Inserting a row whose partitioning column value does not match any LIST partition causes an error. Always include all expected values or add a catch-all partition, and handle new enum values before they appear in production data.

HASH Partitioning

MySQL calculates MOD(expr, num_partitions) to distribute rows evenly. Good for spreading load when there is no natural range or list to partition by.

SQL

CREATE TABLE user_activity (
  user_id   BIGINT NOT NULL,
  activity  VARCHAR(100),
  logged_at DATETIME,
  PRIMARY KEY (user_id, logged_at)
)
PARTITION BY HASH (user_id)
PARTITIONS 8;

LINEAR HASH uses a powers-of-2 algorithm — faster for adding/removing partitions at the cost of slightly less even distribution:

SQL

PARTITION BY LINEAR HASH (user_id)
PARTITIONS 8;

KEY Partitioning

Similar to HASH but uses MySQL's own hashing function and supports non-integer columns. When no column is specified, MySQL uses the primary key.

SQL

-- Partition by primary key automatically
CREATE TABLE sessions (
  session_id VARCHAR(64) NOT NULL,
  user_id    INT,
  data       TEXT,
  PRIMARY KEY (session_id)
)
PARTITION BY KEY()
PARTITIONS 4;

Subpartitioning

You can further divide each partition into subpartitions using HASH or KEY. This creates a two-level partition hierarchy useful for very high-volume tables.

SQL

CREATE TABLE logs (
  log_id   BIGINT NOT NULL,
  log_date DATE   NOT NULL,
  server   INT    NOT NULL,
  message  TEXT,
  PRIMARY KEY (log_id, log_date, server)
)
PARTITION BY RANGE (YEAR(log_date))
SUBPARTITION BY HASH (server) SUBPARTITIONS 4 (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);
-- Creates 3 partitions x 4 subpartitions = 12 physical segments

Partition Pruning

Partition pruning means MySQL skips partitions that cannot possibly contain matching rows. It kicks in automatically when the WHERE clause references the partitioning column with a filterable condition.

SQL

-- MySQL will scan only the p2024 partition
EXPLAIN SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

+----+-------------+--------+------------+-------+
| id | select_type | table  | partitions | rows  |
+----+-------------+--------+------------+-------+
|  1 | SIMPLE      | orders | p2024      | 85423 |
+----+-------------+--------+------------+-------+

Without a filter on the partitioning column, MySQL scans all partitions — which can actually be slower than a non-partitioned table with a good index.

SQL

-- NO pruning — scans all partitions (potentially worse than no partitioning)
EXPLAIN SELECT * FROM orders WHERE amount > 500G
-- partitions: p2021,p2022,p2023,p2024,pmax  <- all scanned

Managing Partitions

Add a new RANGE partition — reorganize the catch-all:

SQL

ALTER TABLE orders
  REORGANIZE PARTITION pmax INTO (
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
  );

Drop a partition — deletes all rows instantly, far faster than DELETE:

SQL

-- Instantly removes all p2021 rows by deleting the partition file
ALTER TABLE orders DROP PARTITION p2021;

Truncate a partition — remove rows but keep partition structure:

SQL

ALTER TABLE orders TRUNCATE PARTITION p2022;

Exchange a partition with a standalone table — useful for zero-copy archiving:

SQL

-- The archive table must have an identical schema (no partitioning)
ALTER TABLE orders
  EXCHANGE PARTITION p2021 WITH TABLE orders_archive_2021;

Check partition distribution:

SQL

SELECT
  PARTITION_NAME,
  TABLE_ROWS,
  DATA_LENGTH,
  INDEX_LENGTH,
  DATA_FREE
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'myapp'
  AND TABLE_NAME   = 'orders'
ORDER BY PARTITION_ORDINAL_POSITION;

Partition Maintenance for Data Archival

The most compelling operational advantage of RANGE partitioning on dates is instant data purging. Instead of a slow DELETE that scans millions of rows and generates undo log:

SQL

-- SLOW: DELETE scans rows, generates undo log, takes minutes on 100M rows
DELETE FROM orders WHERE order_date < '2022-01-01';

-- FAST: DROP PARTITION removes the file, takes milliseconds regardless of row count
ALTER TABLE orders DROP PARTITION p2021;

-- Typical archival job: create archive table, exchange, drop
-- Step 1: ensure archive table exists with same schema
CREATE TABLE orders_archive_2021 LIKE orders;
ALTER TABLE orders_archive_2021 REMOVE PARTITIONING;

-- Step 2: swap partition into archive table (zero-copy, very fast)
ALTER TABLE orders EXCHANGE PARTITION p2021 WITH TABLE orders_archive_2021;

-- Step 3: drop the now-empty partition definition
ALTER TABLE orders DROP PARTITION p2021;

-- Step 4 (optional): compress the archive table
ALTER TABLE orders_archive_2021 ROW_FORMAT=COMPRESSED;

Tip

Schedule a monthly job that adds next month's partition and drops the oldest partition. This keeps the live table lean while preserving history in archive tables.

Limitations of Partitioning

Limitation	Details
Foreign keys	Partitioned tables cannot have or be referenced by foreign key constraints.
UNIQUE constraints	Every unique (including primary key) index must include all partitioning columns.
Full-text indexes	Not supported on partitioned InnoDB tables.
Spatial indexes	Not supported on partitioned tables.
Maximum partitions	A table can have at most 8192 partitions including subpartitions.
Query optimizer	Complex queries may not benefit from pruning — always verify with EXPLAIN.
Joins	Partitioned tables can participate in JOINs but pruning only applies within each table.

Performance Gotchas

Queries that do not filter on the partition key scan ALL partitions — potentially slower than a non-partitioned indexed table.
Global indexes (covering all partitions) do not exist in MySQL — every index is local to its partition.
ALTER TABLE on a partitioned table locks the table by default; use REORGANIZE PARTITION to add future partitions online.
Too many partitions (hundreds or thousands) adds memory and file-handle overhead — stay below a few dozen for most use cases.

When to Use Partitioning

Use RANGE partitioning for time-series data where you regularly purge old records.
Use LIST partitioning when data falls into a small fixed set of categories.
Use HASH/KEY partitioning to spread hot-spot writes across multiple physical files.
Do NOT partition unless the table has tens of millions of rows — overhead outweighs benefit on small tables.
Always verify with EXPLAIN that queries actually trigger partition pruning.

Tip

The biggest operational win from partitioning is instant data purges: dropping a partition removes hundreds of millions of rows in milliseconds by deleting the underlying file, dramatically faster than a DELETE with a WHERE clause.

Partitioning Checklist

Choose a partitioning key that appears in most query WHERE clauses.
Ensure the partitioning column is part of every unique and primary key on the table.
Verify partition pruning with EXPLAIN before deploying.
Plan the maximum number of partitions upfront — reorganizing later has a cost.
If using RANGE on dates, maintain a recurring job to add future partitions before data arrives.
Avoid more partitions than necessary — each partition has overhead in memory and file handles.
Test DROP PARTITION timing on staging before relying on it for production archival SLAs.