Chapter 18 · Partitioning, Large Tables, Archiving, and Data Lifecycle
Partitioning Concepts, Supported Strategies, Partition Pruning, and Design Constraints
Use MySQL partitioning only when its table-organization and pruning behavior matches the workload, and compare it with ordinary indexing rather than treating partitions as automatic sharding or speed.
Learning outcomes
ServiceHub has accumulated a large historical event stream. Most operational queries ask for a recent date range, and retention policy eventually removes whole old periods. A developer proposes “partition the table and it will become fast.” That statement is incomplete. MySQL partitioning changes how one logical table is physically organized; it does not replace indexing, does not create independent database servers, and does not guarantee a better plan.
Define RANGE, LIST, HASH, and KEY-style partitioning and distinguish table partitioning from sharding.
Explain partition pruning and prove which partitions an access path can skip with EXPLAIN evidence.
Compare a pruned partitioned query with the same query on an ordinary well-indexed InnoDB table.
Verify the baseline storage-engine and partition metadata rather than assuming any table can be partitioned.
Recognize when partitioning solves lifecycle/maintenance problems and when an ordinary index is the better tool.
Mandatory work targets one disposable MySQL Community Server 8.4.10 LTS instance. The historical event tables use InnoDB native partitioning. The lab is intentionally append-oriented and has no foreign keys because current MySQL InnoDB partitioning is incompatible with foreign-key constraints; Lesson 2 examines that design consequence directly.
Mental model: one logical table, several internal partitions
A partition is a subset of one table chosen by a deterministic partitioning rule. SQL still addresses a single table name. The optimizer may access one, several, or all partitions. A shard, by contrast, normally means data distributed across independent server or database boundaries with routing, failure, and rebalancing concerns. MySQL table partitioning does not provide those distributed-system properties.
| Strategy | How rows are assigned | Typical fit |
|---|---|---|
| RANGE / RANGE COLUMNS | ordered boundaries such as dates or numeric ranges | time windows, retention boundaries, ordered bands |
| LIST / LIST COLUMNS | explicit value groups | small controlled categories/regions when values map cleanly |
| HASH / LINEAR HASH | server computes a partition from an integer expression | rough distribution when equality on the partition expression matters |
| KEY / LINEAR KEY | server supplies the hash over eligible key columns | distribution without writing your own hash expression |
Partitioning applies to the table data and its indexes together. It is not a mechanism for partitioning only one secondary index. Supported expressions and data types are restricted, so version-specific syntax must be validated against the target server.
Create equivalent plain and partitioned event tables
The comparison needs the same business rows in two structures. The plain table has a normal date-leading index. The partitioned table has the same useful secondary indexes but must include occurred_on in its primary key because the partitioning column must participate in every unique key. Keep that difference in mind; it becomes the central design problem in Lesson 2.
DROP DATABASE IF EXISTS servicehub_lifecycle_lab;CREATE DATABASE servicehub_lifecycle_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE servicehub_lifecycle_lab;CREATE TABLE d10 (n TINYINT PRIMARY KEY);INSERT INTO d10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE TABLE work_order_events_plain ( event_id BIGINT UNSIGNED NOT NULL, occurred_on DATE NOT NULL, work_order_id BIGINT UNSIGNED NOT NULL, customer_id INT NOT NULL, event_type VARCHAR(24) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, payload VARCHAR(180) NOT NULL, PRIMARY KEY (event_id), KEY idx_plain_date_type (occurred_on, event_type, event_id), KEY idx_plain_customer_date (customer_id, occurred_on, event_id)) ENGINE=InnoDB;CREATE TABLE work_order_events_part ( event_id BIGINT UNSIGNED NOT NULL, occurred_on DATE NOT NULL, work_order_id BIGINT UNSIGNED NOT NULL, customer_id INT NOT NULL, event_type VARCHAR(24) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, payload VARCHAR(180) NOT NULL, PRIMARY KEY (event_id, occurred_on), KEY idx_part_date_type (occurred_on, event_type, event_id), KEY idx_part_customer_date (customer_id, occurred_on, event_id)) ENGINE=InnoDBPARTITION BY RANGE COLUMNS(occurred_on) ( PARTITION p2025q4 VALUES LESS THAN ('2026-01-01'), PARTITION p2026m01 VALUES LESS THAN ('2026-02-01'), PARTITION p2026m02 VALUES LESS THAN ('2026-03-01'), PARTITION p2026m03 VALUES LESS THAN ('2026-04-01'), PARTITION p2026m04 VALUES LESS THAN ('2026-05-01'), PARTITION p2026m05 VALUES LESS THAN ('2026-06-01'), PARTITION p2026m06 VALUES LESS THAN ('2026-07-01'), PARTITION pmax VALUES LESS THAN (MAXVALUE));INSERT INTO work_order_events_plain(event_id, occurred_on, work_order_id, customer_id, event_type, region_code, payload)SELECT seq + 1, DATE('2025-12-01') + INTERVAL MOD(seq,210) DAY, 100000 + MOD(seq,6000), 1 + MOD(seq,1500), ELT(1+MOD(seq,4),'created','assigned','status_changed','closed'), 1 + MOD(seq,8), RPAD(CONCAT('servicehub-event-',seq,' '),120,'x')FROM ( SELECT a.n + 10*b.n + 100*c.n + 1000*d.n + 10000*e.n AS seq FROM d10 a CROSS JOIN d10 b CROSS JOIN d10 c CROSS JOIN d10 d CROSS JOIN d10 e) AS numbersWHERE seq < 30000;INSERT INTO work_order_events_partSELECT * FROM work_order_events_plain;ANALYZE TABLE work_order_events_plain, work_order_events_part;SELECT COUNT(*) AS plain_rows FROM work_order_events_plain;SELECT COUNT(*) AS partitioned_rows FROM work_order_events_part;SELECT MIN(occurred_on) AS first_day, MAX(occurred_on) AS last_dayFROM work_order_events_part;Observe the partition definition and row placement
SELECT @@version AS server_version, @@default_storage_engine AS default_engine;SHOW CREATE TABLE servicehub_lifecycle_lab.work_order_events_part\GSELECT PARTITION_ORDINAL_POSITION, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTHFROM information_schema.PARTITIONSWHERE TABLE_SCHEMA='servicehub_lifecycle_lab' AND TABLE_NAME='work_order_events_part'ORDER BY PARTITION_ORDINAL_POSITION;TABLE_ROWS for InnoDB is an optimizer/statistical estimate rather than an exact business count. Use it for trend and rough distribution checks, then use exact COUNT(*) queries when correctness requires exact numbers. SHOW CREATE TABLE is the authoritative way to confirm that the expected partition clause actually belongs to the object you are testing.
SELECT SUM(occurred_on < '2026-01-01') AS before_2026, SUM(occurred_on >= '2026-03-01' AND occurred_on < '2026-04-01') AS march_2026, SUM(occurred_on >= '2026-06-01' AND occurred_on < '2026-07-01') AS june_2026FROM servicehub_lifecycle_lab.work_order_events_part;Partition pruning: prove the optimizer can discard irrelevant partitions
Partition pruning means the optimizer proves that some partitions cannot contain qualifying rows and avoids scanning them. The predicate must expose useful information about the partitioning expression. A March date range should require the March partition, while a predicate unrelated to occurred_on may require all partitions.
EXPLAINSELECT event_id, occurred_on, event_typeFROM servicehub_lifecycle_lab.work_order_events_partWHERE occurred_on >= '2026-03-01' AND occurred_on < '2026-04-01' AND event_type='closed';EXPLAINSELECT event_id, occurred_on, event_typeFROM servicehub_lifecycle_lab.work_order_events_partWHERE customer_id=311;In tabular EXPLAIN, inspect the partitions column. The date-bounded query should name only the partition(s) whose range can contain March dates. The customer-only predicate does not constrain the RANGE partition key, so partition pruning cannot eliminate months merely from customer_id=311. The exact access method and row estimates depend on statistics and the chosen indexes.
Partition pruning is not the same as “faster than an index”
EXPLAIN ANALYZESELECT event_id, occurred_on, event_typeFROM servicehub_lifecycle_lab.work_order_events_plainWHERE occurred_on >= '2026-03-01' AND occurred_on < '2026-04-01' AND event_type='closed';EXPLAIN ANALYZESELECT event_id, occurred_on, event_typeFROM servicehub_lifecycle_lab.work_order_events_partWHERE occurred_on >= '2026-03-01' AND occurred_on < '2026-04-01' AND event_type='closed';Both tables have a date-leading secondary index. A good B-tree range access can already avoid most irrelevant rows on the plain table. Partition pruning can reduce the partitions considered, but it may add no meaningful benefit for this small dataset. Record actual rows, iterator timing, and the chosen index locally; do not claim a fixed speedup. Partitioning often earns its keep through retention and maintenance boundaries rather than through every SELECT.
Tempting but ineffective change: HASH partitioning for a time-range workload
If the dominant operation is “remove or scan old months,” hashing by region destroys the time boundary that makes those operations convenient. The date predicate cannot infer a single hash partition from a region-independent range.
CREATE TABLE servicehub_lifecycle_lab.work_order_events_hash ( event_id BIGINT UNSIGNED NOT NULL, occurred_on DATE NOT NULL, work_order_id BIGINT UNSIGNED NOT NULL, customer_id INT NOT NULL, event_type VARCHAR(24) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, payload VARCHAR(180) NOT NULL, PRIMARY KEY (event_id, region_code), KEY idx_hash_date_type (occurred_on, event_type, event_id)) ENGINE=InnoDBPARTITION BY HASH(region_code) PARTITIONS 8;INSERT INTO servicehub_lifecycle_lab.work_order_events_hashSELECT * FROM servicehub_lifecycle_lab.work_order_events_plain;ANALYZE TABLE servicehub_lifecycle_lab.work_order_events_hash;EXPLAINSELECT event_id, occurred_onFROM servicehub_lifecycle_lab.work_order_events_hashWHERE occurred_on >= '2026-03-01' AND occurred_on < '2026-04-01';The date index can still be useful inside partitions, but the RANGE-on-date pruning advantage is gone. The corrected alternative is not “always use RANGE”; it is to choose organization from the real access and lifecycle problem, then prove the benefit on representative data.
Failure drill: unsupported partition expressions are rejected
Partitioning expressions accept only supported deterministic forms. Do not invent arbitrary functions and hope the server will materialize them.
CREATE TABLE servicehub_lifecycle_lab.bad_partition_expression ( id BIGINT NOT NULL, occurred_on DATE NOT NULL, PRIMARY KEY (id, occurred_on)) ENGINE=InnoDBPARTITION BY RANGE (RAND()) ( PARTITION p0 VALUES LESS THAN (1), PARTITION pmax VALUES LESS THAN MAXVALUE);-- Expected: CREATE TABLE is rejected. Read the server error instead of-- weakening the schema or changing engines just to make the demo pass.The repair is to use a supported expression or a COLUMNS form whose semantics match the domain, such as RANGE COLUMNS(occurred_on). This failure is useful because it establishes a design principle: partition rules are part of table correctness, not just a performance hint.
Production judgment and bridge to Lesson 2
Partitioning is appropriate when a stable partition key aligns with pruning, retention, maintenance, or bulk movement boundaries and when its schema constraints are acceptable. Monitor partition distribution, query plans, per-partition growth, metadata/DDL duration, and the fraction of queries that actually constrain the partition key. Do not use partitioning to compensate for missing indexes or to simulate distributed sharding.
Lesson 2 now confronts the hardest design constraints: every unique key must include the partitioning columns, InnoDB foreign keys cannot coexist with partitioning, and a seemingly natural time key can conflict with global business identity.
Knowledge check
- What is the key difference between MySQL table partitioning and sharding?
- What does partition pruning prove?
- Why compare a partitioned table with an indexed nonpartitioned table?
- Why did HASH(region_code) not help the March retention query?
- What evidence should accompany a partitioning decision?
Reveal answers
- Partitioning divides one logical table inside MySQL; sharding normally distributes data across independent server/database boundaries and requires routing/failure handling.
- That partitions which cannot contain matching values can be skipped for that statement; it does not prove the partitioned table is faster than a well-indexed plain table.
- It prevents attributing ordinary B-tree selectivity benefits to partitioning and reveals whether partitioning adds measurable value for the workload.
- The date predicate does not constrain the hash partition key, so the optimizer cannot prune by month.
- Representative EXPLAIN/EXPLAIN ANALYZE plans, exact/estimated partition distribution, lifecycle operations, write/DDL costs, and schema-constraint review.