Partitioning

Partitioning splits one logical table into multiple physical pieces (partitions) according to a rule on a partitioning key.

Concept

Partitioning splits one logical table into multiple physical pieces (partitions) according to a rule on a partitioning key. Queries with conditions on the key read only relevant partitions (partition pruning), and whole partitions can be dropped instantly. MySQL 8 supports partitioning for InnoDB (native) with RANGE, RANGE COLUMNS, LIST, LIST COLUMNS, HASH, LINEAR HASH, and KEY types, plus subpartitioning.

Why It Matters

Time-series engineering data — CAN frames, telemetry, test logs, DTC events — grows without limit. Partitioning by time makes retention trivial (DROP PARTITION instead of deleting billions of rows) and keeps queries on recent data fast.

Syntax

CREATE TABLE t (...)
PARTITION BY RANGE (expr) (
    PARTITION p0 VALUES LESS THAN (value),
    ...
    PARTITION pmax VALUES LESS THAN MAXVALUE
);
ALTER TABLE t ADD PARTITION (PARTITION pN VALUES LESS THAN (...));
ALTER TABLE t DROP PARTITION p0;
ALTER TABLE t REORGANIZE PARTITION pmax INTO (PARTITION p202607 VALUES LESS THAN (...), PARTITION pmax VALUES LESS THAN MAXVALUE);
ALTER TABLE t TRUNCATE PARTITION p0;
ALTER TABLE t EXCHANGE PARTITION p0 WITH TABLE t_archive;

Example

Rules and limitations:

Every PRIMARY KEY and UNIQUE KEY must include all partitioning columns.

Partitioned InnoDB tables cannot have foreign keys (neither referencing nor referenced).

Maximum 8192 partitions; each partition is a separate .ibd file.

Queries without the partition key scan all partitions (no pruning).

Partitioning is not sharding: all partitions live on the same server.

CREATE TABLE can_log (
    log_id       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id   INT UNSIGNED    NOT NULL,
    captured_at  DATETIME(6)     NOT NULL,
    can_id       INT UNSIGNED    NOT NULL,
    payload      VARBINARY(64)   NOT NULL,
    PRIMARY KEY (log_id, captured_at),                   -- partition key must be part of every unique key
    KEY ix_can_log_vehicle_time (vehicle_id, captured_at)
)
PARTITION BY RANGE COLUMNS (captured_at) (
    PARTITION p2026_01 VALUES LESS THAN ('2026-02-01'),
    PARTITION p2026_02 VALUES LESS THAN ('2026-03-01'),
    PARTITION p2026_03 VALUES LESS THAN ('2026-04-01'),
    PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);

EXPLAIN SELECT COUNT(*) FROM can_log
WHERE captured_at >= '2026-03-01' AND captured_at < '2026-03-08';
-- partitions: p2026_03   ← pruning: only one partition scanned

-- Monthly maintenance: add next month, drop the oldest (retention)
ALTER TABLE can_log REORGANIZE PARTITION pmax INTO (
    PARTITION p2026_04 VALUES LESS THAN ('2026-05-01'),
    PARTITION pmax     VALUES LESS THAN (MAXVALUE)
);
ALTER TABLE can_log DROP PARTITION p2026_01;    -- removes January instantly

SELECT partition_name, table_rows
FRO
…

PARTITION BY LIST COLUMNS (market) (
    PARTITION p_in VALUES IN ('IN'),
    PARTITION p_eu VALUES IN ('DE', 'FR', 'IT'),
    PARTITION p_other VALUES IN ('US', 'UK', 'SG')
);
PARTITION BY HASH (vehicle_id) PARTITIONS 8;     -- even spread
PARTITION BY KEY (vin) PARTITIONS 8;             -- MySQL hashing on any column type

Expected Result

+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2026_02       |    ...     |
| p2026_03       |    ...     |
| p2026_04       |          0 |
| pmax           |          0 |
+----------------+------------+

Common Mistakes

Partitioning small tables (overhead without benefit).

Partition key not in the PK/unique keys (ERROR 1503).

Expecting partitioning to speed up queries that do not filter on the key.

Forgetting to create future partitions (everything lands in pmax).

Best Practices

Partition large time-series tables by time; automate partition rotation with an event (Module 22).

Keep partitions reasonably sized (millions to hundreds of millions of rows).

Check pruning with EXPLAIN (partitions column).

Interview Relevance

Medium: "What is partitioning and pruning?", "Partitioning vs sharding?", "Limitations of partitioning in MySQL?" (FKs, unique keys).

Practice Questions

Partition a dtc_events table by month on detected_at.

Write an event that adds next month's partition and drops partitions older than 12 months.

Explain why PRIMARY KEY (log_id) alone fails for the can_log table.