How to Create and Manage MySQL Partitions

Abdul Wahab Junaid

3 years ago

How to Create and Manage MySQL Partitions

If you’ve ever watched a query crawl through a 200-million-row table when it only needed 50,000 rows, you already understand why partitioning exists. I still remember the first time I partitioned a logging table at 2 AM because a dashboard query was timing out every morning during peak traffic. Within a day, the same query went from 40 seconds to under a second. That’s the kind of difference partitioning makes when it’s used correctly — and the kind of mess it creates when it isn’t.

In this guide, I’m going to walk through everything I’ve learned about MySQL partitioning: what it actually does under the hood, how to set it up, when it helps, when it hurts, and how to keep it healthy in production.

What Partitioning Actually Is

Partitioning splits one logical table into multiple physical pieces, while MySQL still presents it to your application as a single table. Each piece — a partition — stores a subset of rows based on a rule you define, like a date range or a hash of a column value. The MySQL optimizer knows which partitions can possibly contain the rows a query needs, and it skips the rest. This is called partition pruning, and it’s the entire reason partitioning improves performance.

It’s important to be upfront about something: partitioning is not sharding, and it’s not a substitute for indexing. It’s a storage-organization strategy that works with your indexes, not instead of them.

MySQL Architecture Refresher: Where Partitioning Fits

To understand partitioning, it helps to understand where it sits in MySQL’s architecture:

graph TD
    A[Client / Application] --> B[MySQL Server Layer]
    B --> C[Query Parser & Optimizer]
    C --> D[Partition Pruning Logic]
    D --> E[Storage Engine Layer - InnoDB]
    E --> F[Partition 1 - Physical Segment]
    E --> G[Partition 2 - Physical Segment]
    E --> H[Partition N - Physical Segment]

The optimizer decides which partitions to touch before the storage engine ever reads a block from disk. If your WHERE clause aligns with the partitioning key, MySQL eliminates entire partitions from consideration — that’s pruning in action.

Since MySQL 8.0, partitioning is handled natively by the storage engine (InnoDB) rather than through a generic partitioning handler layer, which improved performance and removed several older limitations.

Types of Partitioning in MySQL

MySQL supports four primary partition types:

Partition Type	How Rows Are Assigned	Best For
RANGE	Rows fall into partitions based on a range of column values	Time-series data, logs, orders by date
LIST	Rows fall into partitions based on a discrete set of values	Region codes, status categories
HASH	MySQL applies a hashing function to a column to distribute rows evenly	Even distribution when no natural range exists
KEY	Similar to HASH but uses MySQL’s internal hashing function, supports multiple columns	Similar to HASH, more flexible

There’s also subpartitioning, where you partition a partition (e.g., RANGE partitioned by year, then subpartitioned by HASH on customer ID) for very large datasets.

Setting Up RANGE Partitioning

Let’s say I have an orders table that’s growing fast and mostly gets queried by date.

CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Notice that order_date is part of the primary key. This is a hard requirement in MySQL: every unique key, including the primary key, must include all columns used in the partitioning expression. This trips up almost everyone the first time.

Now if I run:

EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';

Output:

+----+-------------+--------+------------+------+---------------+
| id | select_type | table  | partitions | type | possible_keys |
+----+-------------+--------+------------+------+---------------+
|  1 | SIMPLE      | orders | p2025      | range| PRIMARY       |
+----+-------------+--------+------------+------+---------------+

Only p2025 shows up under partitions — that’s pruning working exactly as intended.

Setting Up LIST Partitioning

LIST partitioning fits well when your data naturally falls into fixed categories.

CREATE TABLE customer_accounts (
    account_id INT NOT NULL,
    region_code VARCHAR(5) NOT NULL,
    account_name VARCHAR(100),
    PRIMARY KEY (account_id, region_code)
)
PARTITION BY LIST COLUMNS (region_code) (
    PARTITION p_north VALUES IN ('NA', 'CA'),
    PARTITION p_europe VALUES IN ('UK', 'DE', 'FR'),
    PARTITION p_asia VALUES IN ('IN', 'CN', 'JP'),
    PARTITION p_other VALUES IN ('AU', 'BR')
);

Setting Up HASH and KEY Partitioning

When there’s no meaningful range or category, and I just want to spread I/O load evenly:

CREATE TABLE session_logs (
    session_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    login_time DATETIME,
    PRIMARY KEY (session_id)
)
PARTITION BY HASH (session_id)
PARTITIONS 8;

With KEY partitioning, MySQL manages the hashing internally and it works well with non-integer columns too:

CREATE TABLE user_events (
    event_id BIGINT NOT NULL,
    user_uuid CHAR(36) NOT NULL,
    event_type VARCHAR(50),
    PRIMARY KEY (event_id, user_uuid)
)
PARTITION BY KEY (user_uuid)
PARTITIONS 6;

Subpartitioning for Very Large Tables

For tables in the hundreds of millions of rows, I’ve combined RANGE and HASH:

CREATE TABLE transactions (
    txn_id BIGINT NOT NULL,
    txn_date DATE NOT NULL,
    account_id INT NOT NULL,
    amount DECIMAL(12,2),
    PRIMARY KEY (txn_id, txn_date, account_id)
)
PARTITION BY RANGE (YEAR(txn_date))
SUBPARTITION BY HASH (account_id)
SUBPARTITIONS 4 (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026)
);

This gives me time-based pruning at the top level and even I/O distribution within each year.

Managing Partitions: Day-to-Day DBA Work

Adding a New Partition

ALTER TABLE orders ADD PARTITION (
    PARTITION p2026 VALUES LESS THAN (2027)
);

Dropping a Partition (Instant Bulk Delete)

This is where partitioning shines operationally. Instead of running a slow DELETE FROM orders WHERE order_date < '2023-01-01', I just drop the partition:

ALTER TABLE orders DROP PARTITION p2023;

This is nearly instantaneous because MySQL just removes the physical segment — no row-by-row deletion, no massive undo log, no replication lag from a giant DELETE statement.

Reorganizing Partitions

ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Checking Partition Status

SELECT PARTITION_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';

Storage Engine and Indexing Considerations

Every partition in InnoDB is essentially its own B-tree structure for the clustered index and its own set of secondary index structures (as of MySQL 8.0’s native partitioning, secondary indexes are local to each partition). This has real consequences:

Local indexes only. MySQL doesn’t support global secondary indexes across partitions. A secondary index lookup that doesn’t include the partitioning column may need to scan every partition.
This is why the partition key almost always needs to appear in your most common WHERE clauses — otherwise you lose pruning and gain overhead instead.
InnoDB’s buffer pool caches pages per partition, so a well-pruned query touches fewer pages and uses the cache more efficiently.

Real-World Scenario: Log Table Retention

A common professional DBA workflow I use constantly: application logs partitioned by month, with an automated job that drops partitions older than 90 days and adds a new partition each month.

-- Monthly cron job
ALTER TABLE app_logs ADD PARTITION (
    PARTITION p202608 VALUES LESS THAN (TO_DAYS('2026-09-01'))
);

ALTER TABLE app_logs DROP PARTITION p202502;

This keeps the table lean without ever running a blocking DELETE on a huge table.

Performance and Optimization Tips

Always check EXPLAIN and confirm partitions shows only the ones you expect — if it lists all partitions, your query isn’t pruning.
Keep the number of partitions reasonable. MySQL supports up to 8192 partitions per table, but performance and maintenance overhead grow with count; I rarely go past a few dozen.
Avoid partitioning small tables (under a few million rows) — the overhead outweighs the benefit.
Don’t partition by a column that’s rarely used in WHERE clauses; you’ll get all the overhead and none of the pruning benefit.
Combine partitioning with proper secondary indexes on each partition for point lookups.

Common Pitfalls and Troubleshooting

“Unique key doesn’t include all partitioning columns” error. This is MySQL’s strict rule — every unique/primary key must include the partitioning columns. The usual fix is a composite primary key like I used above.

Query not pruning. Check that your WHERE clause uses the exact partitioning expression or a compatible one. WHERE YEAR(order_date) = 2025 prunes fine when partitioned by YEAR(order_date), but a mismatched function or implicit type conversion can defeat pruning.

Too many small partitions. I’ve seen teams partition by day for years, ending up with thousands of tiny partitions, which actually slows down metadata operations. Match partition granularity to your actual query and retention patterns.

Foreign keys. Partitioned InnoDB tables do not support foreign key constraints referencing or being referenced in certain configurations — validate this before designing your schema.

Security Considerations

Partitioning itself isn’t a security boundary — don’t rely on it to isolate tenant data for access control; use proper application-level authorization and row-level security patterns instead. Grant privileges are still table-level, not partition-level, in standard MySQL.

Frequently Asked Questions

Does partitioning improve every query? No. Partitioning helps most when queries filter on the partitioning column and the table is large. For queries that scan unrelated columns, it adds overhead without benefit.

Can I partition an existing table with data? Yes, using ALTER TABLE ... PARTITION BY .... Be aware this rewrites the entire table, which can be slow and locking on large tables — plan for a maintenance window or use online schema change tools.

Is partitioning the same as sharding? No. Partitioning happens within a single MySQL instance/table. Sharding distributes data across multiple separate database instances or servers.

What’s the maximum number of partitions? 8192 for most storage engines in modern MySQL versions, though practical limits are much lower.

Interview Questions on MySQL Partitioning

What is partition pruning, and how does the optimizer decide which partitions to scan?
Why must partitioning columns be part of every unique key in a partitioned table?
Explain the difference between RANGE, LIST, HASH, and KEY partitioning with use cases.
How does dropping a partition differ from running a DELETE statement in terms of performance and replication?
Why don’t partitioned InnoDB tables support global secondary indexes?
When would you choose subpartitioning over a single-level partitioning scheme?
How would you migrate a large, unpartitioned production table to a partitioned one with minimal downtime?

Summary and Key Takeaways

Partitioning is one of those features that looks simple on the surface but has real architectural implications once you dig in. The core ideas to remember:

Partitioning splits a table into physical segments while keeping one logical table for your application.
Partition pruning is the entire performance win — design your partitioning key around your actual query patterns.
RANGE and LIST suit natural categories like dates or regions; HASH and KEY suit even distribution.
Dropping a partition is a near-instant way to purge old data compared to bulk DELETEs.
Indexes are local to each partition in InnoDB, so plan your WHERE clauses accordingly.
Partitioning is not sharding, and it’s not a security boundary.

Used well, partitioning turns unmanageable tables into predictable, maintainable ones. Used carelessly, it just adds complexity. Test with EXPLAIN, monitor INFORMATION_SCHEMA.PARTITIONS, and let your real query patterns — not guesses — drive your partitioning key.