MySQL Table Partitioning Setup: RANGE, LIST, HASH

Why MySQL partitioning stops helping

MySQL RANGE partitioning by date is a workable scheme right up to the moment when queries start going across the entire table. As long as the WHERE condition contains the partitioning key, the optimizer prunes unnecessary sections and reads one or two segments.

As soon as the filter goes by another column — by client, status, external identifier — pruning does not kick in, and instead of a portion of the data, all hundreds of millions of rows are scanned. This is exactly where query degradation begins, which is already noticed by API response time.

The second typical failure point is purging historical data. DELETE FROM ... WHERE created_at < ... on a large table runs for hours, holds locks, bloats the undo log and replica lag.

The freed space is not returned to disk: the tablespace files remain the same size, and disk overflow arrives before the data runs out. Replica lag at this moment grows avalanche-like, because every deleted row is written to the binary log.

The third failure is the wrong strategy. HASH and KEY provide even distribution, but do not provide pruning by date range: purging old periods turns into the same mass operation. LIST is convenient for discrete values, however adding a new value requires rebuilding the section.

If the type is chosen for the current load profile rather than for how the data will be deleted, the scheme breaks on the very first growth.

Let's break down how this is addressed: choosing the right partitioning type, maintaining sections instead of deleting rows, and controlling replica lag.

What a well-designed table partitioning scheme delivers

Range partitioning turns a period-based query from a full table scan into a read of one or two partitions: the planner prunes unnecessary sections based on the query predicate, and response time drops in proportion to the share of data you have pruned.

On a table holding several years of history, a typical "last month" report speeds up severalfold without a single new index — purely because the relevant rows are stored compactly.

Deleting old data stops being a write operation. Instead of a DELETE on millions of rows with every deletion subsequently replicated and the log bloated, you drop an entire partition — this is a metadata operation, it completes in seconds and frees space immediately.

The replication load drops as well: replicas do not have to replay a multi-minute stream of deletions, lag does not grow, and the maintenance window shrinks from hours to minutes.

Write stability benefits too. Inserts go into the "hot" partition, where indexes are more compact than in a fully grown table, so the index tree stays shallow and insert performance does not degrade over time.

The failure class caused by running out of disk goes away: space is freed on schedule rather than at the moment free space runs out.

Table maintenance — index rebuilds, statistics collection, integrity checks — runs per partition rather than across the whole table, and does not block the remaining data.

The boundaries matter more than the fact of partitioning itself. The key must appear in query predicates, otherwise pruning will not kick in and you will get the same full scan plus overhead.

Unique keys must include the partitioning column, and the number of partitions should be kept within reasonable limits — hundreds of sections slow down table opening and planning.

RANGE, LIST, HASH and composite keys: what to choose

The choice of mode is determined by the nature of the data and the operations performed on it. RANGE slices the table into continuous ranges — dates, identifiers, accumulated values. LIST works with discrete sets: region, status, document type.

HASH distributes rows evenly by the remainder of division when there is no natural range but a predictable size for each section is needed.

The limitations are common to all modes and should be remembered before, not after. Foreign keys to a partitioned table are not supported — referential integrity is moved to the application or trigger level.

Unique indexes must include the partitioning field in full: a composite key (partition_field, identifier) solves the problem but changes the logic of queries.

Sections cannot be mixed by type within one table, and the number of partitions is kept within reasonable limits — hundreds of sections noticeably slow down opening the table and query planning.

A practical guideline: if 90% of queries filter by date — use RANGE, if by category — LIST, if there is no filter and evenness matters — HASH. A composite key is added on top of any mode when uniqueness across several fields needs to be preserved.

How we implement partitioning: stages of work

Partitioning doesn't start with ALTER TABLE.

First you need to understand which key the query actually uses and which queries against the table even exist in production — otherwise splitting by date while filtering by user_id will result in a full scan of all partitions instead of pruning. That's why the first steps are analyzing the schema and the workload profile, not editing the DDL.

1. Schema and query audit. We look at the table structure, indexes, foreign keys, triggers, and views tied to it. We collect the slow log and execution statistics: which queries read the table, with what conditions in WHERE, whether there are aggregates across the whole table.
2. Workload profile and key selection. We determine the data distribution across the candidate partitioning key — usually a date or an ID range. We verify that most heavy queries filter by exactly this field, otherwise there's no point in splitting.
3. Partition scheme design. We choose the type: RANGE by date for logs and history, LIST for a fixed set of values, HASH for even distribution without a natural boundary. We calculate the number of partitions and boundaries so that each holds a comparable volume.
4. Migration. We move the table to the new structure with a minimal downtime window: we prepare partitions in advance, transfer data in batches, synchronize the changes accumulated during the transfer, and only then switch the application to the new table.
5. Verification on a copy. We run the migration on a full copy of production: we compare checksums, execution plans before and after, and the response time of key queries. We make sure that partition pruning actually works and isn't just on paper.
6. Maintenance setup. We set up regular addition of new partitions and removal of outdated ones — via a scheduler event or an external job. Old data is removed by dropping a partition, not by a mass DELETE, which holds locks and bloats the log.
7. Handover of procedures. We hand over the documentation: partition map, rules for adding and removing, rollback procedure, control points. The client's team then maintains the table on its own, without us.

Each stage ends with an artifact — a workload report, a partition scheme, a migration plan. This allows stopping at any step and returning to it without rebuilding the solution from scratch.

What the client receives after the work

After the work, you are left not with a "set of executed commands" but with a working system that can be handed over to any engineer. We put all artifacts into your repository and wiki — with no dependency on us.

• Partitioning configurations — ready-to-use DDL scripts for creating and rebuilding partitions according to the chosen strategy (RANGE by dates, LIST by reference tables, HASH/KEY for even distribution), with comments explaining why exactly this partitioning key was chosen.
• Partitioning scheme and data map — a document showing which table is partitioned how, by which field partition pruning occurs, and which indexes must include the partitioning key.
• Retention policies — lifecycle rules: how many partitions we keep "hot", when outdated ones go to archive, and what we do with historical data instead of deleting it.
• Maintenance scripts — automatic creation of future partitions in advance, detaching and archiving old ones, integrity checks, plus wrappers for scheduled execution.
• Monitoring and alerts — metrics on the number of partitions, size, whether queries hit partition pruning, and notifications when approaching boundaries.
• Operations regulations — step-by-step instructions: how to add a partition manually, how to roll back a failed rebuild, and what to do if the table grows faster than forecast.
• Verification report — before-and-after measurements on real queries: query execution time, execution plan, and the volume of data scanned.

Each artifact is tested on your stand, not written "from a template". We hand it over with a walkthrough: an engineer on your team must be able to maintain the scheme without us.

Case study: speeding up queries and simplifying data cleanup

The client is an event analytics service: a log table with hundreds of millions of rows, no partitioning was used, and old records were deleted manually with scripts.

Queries by the "last month" period took tens of seconds and degraded as the table grew, while cleanup ran at night and still competed with production queries.

What was changed

The table was split by date ranges — one section per month, with a rule that pre-creates sections several periods ahead. Indexes were rebuilt so that date conditions prune unnecessary sections at the query parsing stage rather than after reading the entire table. For hot reports, partitioning by key value ranges was added so that fresh data stays compact.

Cleanup was switched from row-by-row deletion to section truncation: an expired period is detached and dropped entirely, without scanning rows and without loading the change log. The procedure was tied to a schedule and a boundary check — it does not touch current data.

The result in numbers

• Query response time by period dropped from tens of seconds to fractions of a second — the query reads one or two sections instead of the entire table.
• Cleaning up old data takes minutes instead of hours and runs without blocking production queries.
• The size of the active part of the table stopped growing linearly: hot data is separated from the archive.
• Rollback became simpler — if there is an error in the rules, it is enough to restore the truncated section from a backup without restoring the entire table.

We have honed this approach on projects with a similar load profile — for example, when migrating storage for ClickHouse and building analytics environments.

The key here is not partitioning itself, but the consistency of the section schema with real queries: if query conditions do not match the partitioning key, there will be no gain.

How is partitioning different from sharding?

Partitioning is the logical division of a single table into sections within one server.

From the application's point of view, the table remains a single object: queries go by the same name, the schema does not change, and the driver and ORM know nothing about the internal structure.

The DBMS itself prunes unnecessary sections by key (partition pruning), and a query by date range reads only its own section instead of the entire table.

Sharding is the distribution of data across several independent servers, where each node stores its own portion of rows and serves its own stream of queries.

The difference shows up in who is responsible for routing.

With partitioning, the DBMS itself does this: the application writes and reads as before, and the gain comes from section pruning and local operations — faster deletion of old data through section pruning instead of mass row deletion, less load on indexes.

With sharding, routing is handled either by a layer in the application or by an intermediate layer: you need to choose a sharding key, think through how the data will be distributed, and what to do with queries that touch several nodes.

Operations differ no less. A partitioned table lives on one server: backup, replication, version upgrades, and monitoring remain familiar, and adding a new section is a planned operation.

A sharded cluster requires orchestration: rebalancing under uneven growth, consistency between nodes, a separate backup strategy for each shard, and handling failures of part of the cluster. Complexity grows with the number of nodes.

The practical takeaway: partitioning solves the problems of volume and maintenance of a single table — growth in the number of rows, clearing history, speeding up queries by the partitioning key.

Sharding is needed when what you hit is not the size of the table but the throughput of a single server: writes, reads, or a volume that physically does not fit on one node.

They are often used together — first partition within a node, and when the resource is exhausted, distribute the sections across servers.

Common technical concerns during implementation

Partitioning affects not only the table structure, but also how the planner chooses the read path, how the replica transfers changes, and how the application behaves during a rollback. Below are the things most often checked before implementation.

How will this affect replication?

The replica receives the same changes in the same order — the fact of partitioning itself does not affect the transfer stream.

What matters is something else: operations that rebuild sections (for example, reorganization when adding a new range) run on the source and are repeated on the replica, so it is better to perform them during a low-load window. We check replica lag before and after and make sure it remains within acceptable limits.

Will the application have to be rewritten?

In most cases — no. The schema remains the same table with the same name, and the application continues to write and read as before. Changes are needed in a targeted way: if queries do not use the partitioning key, they should be supplemented with a condition on that key. We flag such places and propose minimal edits, rather than rewriting the data access layer.

Can the changes be rolled back?

Yes. Partitioning is reversible: sections can be merged back into a single table, and a full copy is taken before any changes. We fix the rollback plan before work begins and test it on a test environment so that returning to the original state takes a predictable amount of time.

What risks remain?

The main one is choosing an unsuccessful key: sections that are too small produce many overhead operations, while sections that are too large provide no gain. The second is forgotten queries without the key that continue to read the entire table.

Both risks are eliminated at the stage of schema design and execution plan verification, before the changes reach the production database.

Let's discuss your MySQL partitioning task

To come back with an estimate and a work plan, rather than generalities, we need baseline data on your database. Partitioning is a decision made based on the actual workload profile: how tables grow, which queries dominate, whether partitioning or sharding already exists. The more precise the inputs, the fewer iterations for alignment.

What's worth preparing before the conversation:

• Schemas of candidate tables: SHOW CREATE TABLE for the largest and fastest-growing ones.
• Approximate volumes: number of rows and space used per table.
• Query profile: which selects run by date range, which by a specific key, where the bottlenecks are now.
• Current indexes and constraints: unique keys, foreign relationships — they directly affect the choice of strategy.
• Server version and edition, replication mode, fault-tolerance requirements.
• Maintenance window constraints: how long the database can be unavailable for rebuilding.
• Target behavior: what should happen to old data — archiving, deletion, moving to cold storage.

Based on this data, we determine the appropriate partitioning type — RANGE by date, LIST by category, or HASH by distribution key — and check whether the split conflicts with existing unique indexes.

Then we prepare a migration plan: rebuilding without prolonged locking, the switchover order, rollback points.

Describe your task — we'll come back with an estimate, a work plan, and start dates.