MySQL Database Partitioning: A Complete Guide

A table holding three years of order history grows past 200 million rows. Every night’s cleanup job runs DELETE FROM orders WHERE created_at < '2023-01-01', and it locks the table for forty minutes while the binlog floods the disk. This is the classic moment database partitioning earns its keep. This article walks through MySQL partitioning from “what it is” to “when you shouldn’t use it,” clarifies restrictions from the official docs that are easy to overlook, and briefly touches on how other databases, like PostgreSQL, approach the same problem.

What Problem Partitioning Actually Solves

MySQL’s own documentation defines partitioning plainly: it’s a horizontal partitioning mechanism that distributes different rows of the same table across separate physical partitions according to rules you define, with each partition effectively stored as an independent chunk at the filesystem level — while remaining, logically, one single table with no change to your SQL syntax. Worth noting: MySQL currently does not support vertical partitioning (splitting different columns off into different storage locations), and the two are often conflated — worth clearing up first.

Partitioning’s real value comes down to three things. First, it lets a single table hold more data than a single disk or filesystem partition could otherwise handle. Second, it makes deleting old data dramatically faster — rather than running DELETE against 200 million rows, you can run ALTER TABLE ... DROP PARTITION against an entire partition, which is essentially dropping the underlying file and completes almost instantly, without generating a massive undo log or binlog. Third, and most commonly cited, is partition pruning — when a query’s WHERE clause matches the partition key, MySQL can exclude partitions that couldn’t possibly contain matching rows and scan only the relevant ones, effectively getting an index-level optimization for free.

Storage Engine Restrictions: Not Every Table Can Be Partitioned

As of MySQL 8.0, only the InnoDB and NDB storage engines support partitioning — older engines like MyISAM, MERGE, CSV, and FEDERATED are all unsupported. Every partition under the same partitioned table must also use the same storage engine; you can’t mix InnoDB on one partition with a different engine on another. If your project is still running on an older version with some tables on MyISAM, the first step is confirming whether they can be converted to InnoDB — an easy-to-overlook prerequisite before you even start planning partitions.

Four Core Partition Types

RANGE Partitioning: the Default Choice for Time-Series Data

RANGE partitioning splits data according to value ranges on a column, most classically used to split data by month or year:

CREATE TABLE orders (
    id INT NOT NULL,
    created_at DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

Log tables, order tables, transaction history — anything where “newer data gets queried more, older data should get purged” — is close to the textbook scenario for RANGE partitioning. There’s also a variant called RANGE COLUMNS, which lets you define ranges directly using multiple columns and non-integer types (like DATE or VARCHAR), without needing to first convert values to integers via a function the way basic RANGE partitioning requires — offering more flexibility.

LIST Partitioning: Discrete Value Classification

LIST partitioning follows similar logic to RANGE, except it’s based not on a continuous range but on an explicitly enumerated set of discrete values — a good fit for scenarios like partitioning by region code or store ID, where you have a finite, known set of categories. It also has a LIST COLUMNS variant supporting multiple columns and non-integer types.

HASH and KEY Partitioning: Spreading Data Evenly

When your goal isn’t query optimization but rather spreading data evenly to avoid a single hot partition, HASH partitioning computes a hash based on an expression you specify (e.g., HASH(id)) to determine which partition a row goes into. KEY partitioning is more hands-off — you specify only the columns, and MySQL supplies its own internal hashing function rather than requiring you to write an expression. Both have LINEAR HASH / LINEAR KEY variants, which reduce the cost of redistributing data when you add or remove partitions, at the cost of less even data distribution compared to the non-linear versions.

Subpartitioning: Partitions Within Partitions

MySQL also supports a second layer of subpartitions underneath an existing partition scheme (for example, partitioning by RANGE on year, then further splitting each yearly partition by HASH on user ID). This suits scenarios with extremely large data volumes where a single partitioning dimension is still too coarse, but it adds meaningful complexity, and most projects never actually need this layer.

No Oracle-Style INTERVAL Here: How Partitions Keep Up With Time

Oracle offers INTERVAL partitioning syntax — whenever data for a new month comes in, the database automatically creates a new partition for it. MySQL has no such mechanism natively: RANGE partition boundaries must exist ahead of time, and the moment data falls outside every existing partition’s range, INSERT simply fails with an error rather than auto-generating a partition. That means any table RANGE-partitioned by date needs an ongoing plan for “keep adding partitions ahead of time,” or it will eventually run into a “no matching partition” insert failure. In practice, there are three common approaches:

Approach 1: Cyclical Partitioning (12 Fixed Partitions — Simplest, With a Tradeoff)

Use the MONTH() function to bucket data into 12 fixed partitions by month (1–12); once set up, in theory you never touch it again:

CREATE TABLE failed_jobs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    failed_at DATETIME NOT NULL,
    payload LONGTEXT,
    PRIMARY KEY (id, failed_at)
)
PARTITION BY HASH(MONTH(failed_at))
PARTITIONS 12;

There’s an easy-to-miss cost here: data from August 2025 and August 2026 land in the same partition, because the partitioning basis is purely the cyclical “month” number — the year is never factored in at all. If your goal is using DROP PARTITION to quickly purge “data older than two years,” this approach can’t do that at all, since every partition permanently mixes data from multiple different years. It’s better suited to scenarios where you just want to spread data out, without relying on partitioning for retention management.

Approach 2: Pre-Create Partitions for the Next Several Years

Write out month-by-month partitions for the next 5–10 years at table creation time, with a MAXVALUE catch-all partition at the end as a safety net:

CREATE TABLE failed_jobs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    failed_at DATETIME NOT NULL,
    PRIMARY KEY (id, failed_at)
)
PARTITION BY RANGE COLUMNS(failed_at) (
    PARTITION p202601 VALUES LESS THAN ('2026-02-01'),
    PARTITION p202602 VALUES LESS THAN ('2026-03-01'),
    -- continue this pattern out to future years
    PARTITION p203012 VALUES LESS THAN ('2031-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

This approach needs no extra scheduling and stays stable for years once set up; the downside is you still have to come back and maintain it manually once the pre-built years run out. And because it leaves behind a p_future MAXVALUE partition, if you later want to switch to Approach 3’s automated scheduling to add partitions, keep in mind that MySQL will not let you use ADD PARTITION on a table that already has a MAXVALUE partition — doing so corrupts the table and breaks querying. The correct move is ALTER TABLE ... REORGANIZE PARTITION p_future INTO (...), which splits the MAXVALUE partition back into “a new partition plus a new MAXVALUE partition.”

Approach 3: MySQL Event Scheduler Automation (Most Proper, But Two Prerequisites to Watch)

Pair a stored procedure with MySQL’s built-in EVENT scheduler so the database automatically generates next month’s partition every month:

DELIMITER //
CREATE PROCEDURE auto_create_next_month_partition()
BEGIN
    DECLARE next_month_first_day VARCHAR(10);
    DECLARE partition_name VARCHAR(20);

    SET next_month_first_day = DATE_FORMAT(NOW() + INTERVAL 2 MONTH, '%Y-%m-01');
    SET partition_name = CONCAT('p', DATE_FORMAT(NOW() + INTERVAL 1 MONTH, '%Y%m'));

    SET @sql = CONCAT('ALTER TABLE failed_jobs ADD PARTITION (PARTITION ', partition_name, ' VALUES LESS THAN (\'', next_month_first_day, '\'))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS evt_auto_partition
ON SCHEDULE EVERY 1 MONTH STARTS '2026-08-25 00:00:00'
DO
  CALL auto_create_next_month_partition();

This is the fully automated route — every month gets its own partition, and cleaning up old data later is just an instant DROP PARTITION. But there are two prerequisites that are easy to overlook. First, enabling the event_scheduler global variable requires the SUPER privilege, and creating the EVENT itself requires a separate EVENT privilege — you’ll need to confirm both are actually available with your DBA or cloud database provider, since a fair number of managed cloud MySQL services restrict or disable the event scheduler by default. Second, the stored procedure above uses ADD PARTITION, which only works if the table does not already have a MAXVALUE catch-all partition; if the table definition already includes one (as in Approach 2), you’d need to switch ADD PARTITION to REORGANIZE PARTITION, or the scheduled job will corrupt the table the first time it runs. It’s also worth adding a separate monitoring job that periodically checks whether the newest partition’s boundary is still “far enough ahead” — if the scheduled job ever silently fails or skips a month, inserts for that month will start failing outright.

Which One Should You Pick

For moderate data volumes with no urgency around automation, Approach 2 is the least effort. For scenarios with large enough data volumes that genuinely need periodic DROP PARTITION cleanup, Approach 3 is the most complete architecture — but confirm your database privileges first, and pair the automated partition-adding schedule with a table definition that has no MAXVALUE partition. Consider both together; don’t just copy the syntax without matching it to your table structure.

How Partition Pruning Works, and How It Fails

Whether partition pruning kicks in hinges entirely on whether the query’s WHERE clause includes the partition key. Using the orders example above, a query like WHERE created_at >= '2026-01-01' lets MySQL skip the p2024 and p2025 partitions entirely; but if the query condition never mentions created_at at all — say, WHERE amount > 1000 — MySQL has to scan every single partition to produce a correct result. In that case, partitioning doesn’t speed up the query at all; it adds overhead from opening and checking more files instead. To check whether pruning actually kicked in, prefix your query with EXPLAIN and look at how many partitions actually show up in the partitions column of the output.

MySQL also supports explicit partition selection syntax, letting you tell the server to query only specific partitions directly:

SELECT * FROM orders PARTITION (p2026) WHERE amount > 1000;

This syntax also works with DELETE, UPDATE, INSERT, and REPLACE — when you already know exactly which partition your data lives in, it saves the server from having to figure that out itself.

The Two Easiest Traps to Fall Into

Trap one: the unique key constraint. MySQL requires that any column used in the partitioning expression appear in every unique key on the table, including the primary key. That means if a table has both PRIMARY KEY(id) and UNIQUE KEY(email), you almost certainly can’t partition on any column other than id, unless you drop the unique constraint on email or find a way to fold the partition column into every unique key. This restriction frequently kills partitioning plans dead in their tracks when retrofitting an existing system — make sure to inventory every unique key on the table before you start.

Trap two: foreign keys aren’t supported at all. Any InnoDB table with user-defined partitioning can neither be referenced by another table’s foreign key, nor define a foreign key referencing another table itself. If your database leans heavily on foreign keys for referential integrity, you’ll need to decide upfront whether to replicate that logic at the application layer or simply accept dropping the foreign key constraints before adopting partitioning.

Beyond these two hard limits, there’s a practical issue that often gets underestimated: more partitions isn’t automatically better. Each partition is its own file at the filesystem level, so as the partition count grows, the overhead of opening files and maintaining metadata accumulates. A general rule of thumb is to stay within the range of a few dozen to one or two hundred partitions — well before you hit that order of magnitude, it usually means your partitioning dimension or granularity needs a redesign, not more partitions piled on top.

A Quick Comparison With Other Databases

Every major database has its own partitioning mechanism, with slightly different design philosophies — briefly:

• PostgreSQL: since version 10, PostgreSQL has supported declarative partitioning, conceptually similar to MySQL’s approach, with RANGE, LIST, and HASH partition types of its own. The key difference is that PostgreSQL treats mounting and unmounting a partition (ATTACH PARTITION / DETACH PARTITION) as a pure metadata operation with no actual data movement — which generally makes it lighter-weight and more flexible than MySQL when operating on very large tables.
• Oracle Database and SQL Server: both have a longer history with partitioning and support more advanced features like global indexes (indexes maintained across partitions, unlike MySQL’s default of local, per-partition indexes). Their partitioning feature sets are generally considered more complete than MySQL’s, but come with a correspondingly higher licensing cost — most small-to-midsize projects wouldn’t switch databases purely to get better partitioning.

A Simple Rule for Deciding Whether to Partition

Partitioning isn’t a cure-all, and it isn’t a tool for fixing “a single database server can’t handle the traffic” — it solves scale problems around storage, querying, and cleanup for a single table. Write throughput and server load bottlenecks still need to be handled with read/write splitting, caching, or sharding across multiple servers. The real signal that partitioning is worth considering is usually this: a table’s data volume has grown large enough that whole-table operations (backups, deletes, schema changes) are visibly dragging down operations, and query patterns are heavily concentrated on a predictable column — most commonly a timestamp. That’s when partitioning can actually deliver its value: trading pruning for query speed, and DROP PARTITION for fast cleanup. On the other hand, if your query conditions are all over the map and rarely hit a consistent partition key, adopting partitioning prematurely will likely just split one table into several smaller files without delivering any real benefit.