CONCEPT Cited by 1 source

Key partitioning¶

Key partitioning is a MySQL partitioning strategy that lets MySQL itself pick the partitioning function — an internal hash algorithm operating on the table's primary key (or another unique key) — to distribute rows across a specified number of partitions. The operator declares only the partition count; the mapping from key value to partition number is opaque to the application.

The `CREATE TABLE` shape¶

Morrison's library_books worked example:

CREATE TABLE library_books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    ...
)
PARTITION BY KEY()
PARTITIONS 4;

(Source: sources/2026-04-21-planetscale-what-is-mysql-partitioning)

Empty KEY() tells MySQL to use the primary key as the partition key. An explicit PARTITION BY KEY(col_a, col_b) is permitted when a non-PK unique-enough column set is preferred. PARTITIONS 4 declares the number of partitions.

Why reach for it¶

Morrison's framing verbatim: "This is where the KEY partitioning strategy works well. By using KEY, you are letting MySQL use the primary key (or a unique key) of a table to determine how to sort the data. An internal algorithm will be used to determine how to best sort the data evenly across a specified number of partitions." (Source: sources/2026-04-21-planetscale-what-is-mysql-partitioning)

The virtue is zero-configuration. Operators who want partitioning's per-partition maintenance benefits (faster TRUNCATE PARTITION, per-partition backups, per-partition index rebuilds) and cross-partition scan parallelism, but who don't want to define value ranges (RANGE) or enumerate value sets (LIST), pick KEY.

Partition pruning¶

KEY partitioning still permits partition pruning, but only on equality predicates on the partition key. WHERE book_id = 12345 hashes 12345 through MySQL's internal algorithm to determine the target partition and scans only that one. Range predicates (WHERE book_id BETWEEN 1000 AND 2000) don't prune — the hash smears consecutive IDs across all partitions.

Distinction from HASH partitioning¶

MySQL also supports PARTITION BY HASH (expr) PARTITIONS N, where expr is an operator-supplied hash function. KEY partitioning is the MySQL-picks-the-hash-function variant; HASH is the operator-picks-the-hash-function variant. Morrison's post doesn't name HASH — his walk covers the three main strategies RANGE / LIST / KEY and defers other strategies to the MySQL manual.

Distinction from hash sharding¶

KEY partitioning at the same-server altitude is the sibling of hash sharding at the cross-server altitude:

Axis	KEY partitioning	Hash sharding
Physical unit	File on one server	Separate server / cluster
Hash function	MySQL-internal (opaque)	Framework-specified (often CRC32, xxhash, etc.)
Fault isolation	None — one server, one fate	Cross-server — one shard down ≠ all shards down
Scales	Vertically	Horizontally
Rebalancing on count change	`ALTER TABLE` (rewrites whole table)	`reshard` workflow (Vitess)

See concepts/hash-sharding for the cross-server sibling.

Trade-offs¶

Even distribution without prior knowledge — the hash smears both monotonic auto-increment IDs and skewed value distributions evenly across partitions. No hotspot from "new rows all land in the same partition" failure mode of RANGE on monotonic IDs.
No range-scan benefit — sequential values scatter, so WHERE id BETWEEN 1000 AND 2000 scans all partitions.
Changing PARTITIONS N — changing the partition count rewrites the whole table, since the hash-to-partition mapping depends on N. Contrast HASH sharding via Vitess's keyspace-id approach where shard-count changes are decoupled from the hash function.

Seen in¶

sources/2026-04-21-planetscale-what-is-mysql-partitioning — Brian Morrison II (PlanetScale, 2023-10-10) canonicalises the KEY strategy on a library_books example with PARTITION BY KEY() PARTITIONS 4; names the "let MySQL figure it out" zero-configuration property as the reason to pick it over RANGE or LIST.