CONCEPT Cited by 1 source

Range partitioning¶

Range partitioning is a MySQL partitioning strategy that assigns rows to partitions based on whether a column value falls inside a pre-declared numerical range. Each partition owns a contiguous range of the partition-key space, declared per-partition with VALUES LESS THAN (N); the endpoints of the whole space are bracketed with MINVALUE and MAXVALUE to cover values outside any explicit range.

The `CREATE TABLE` shape¶

Morrison's library_books worked example:

CREATE TABLE library_books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255),
    publication_year INT,
    ...
)
PARTITION BY RANGE (publication_year) (
    PARTITION p0 VALUES LESS THAN (2001),
    PARTITION p1 VALUES LESS THAN (2011),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

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

Rows with publication_year in [MINVALUE, 2001) land in p0, [2001, 2011) in p1, [2011, 2021) in p2, [2021, MAXVALUE] in p3. Each partition's file on disk holds a contiguous numerical range.

When it fits¶

Time-like columns — created_at, publication_year, event_date, order_date. Monotonic over time; natural alignment with archival and retention policies (drop-partition for data older than N years).
Predictable distributions — ranges can be drawn so each partition receives a roughly equal share of data and traffic.
Range scans — queries like "all books published between 2001 and 2010" benefit from scanning exactly one partition via partition pruning.

When it fails¶

Monotonically-increasing IDs — sequential auto-increment values all land in whichever partition owns the current max — the active write-frontier pins to one partition. Same failure mode as range-sharding on monotonic IDs at the sharding altitude.
Skewed distributions — if most rows cluster in a narrow range (e.g. most users active in the last 2 years of a 10-year table), the active partitions are hot while archival partitions are cold.
Drifting distributions — today's active range is tomorrow's archive. Without periodic ALTER TABLE ... REORGANIZE PARTITION, ranges become unbalanced over time.

Partition pruning¶

The canonical partition-pruning example: "if you wanted to query all books published in 2007, MySQL would only need to scan p1 and not the entire table to find the requested data" (Source: sources/2026-04-21-planetscale-what-is-mysql-partitioning). The planner uses the PARTITION BY RANGE (publication_year) metadata to determine which partitions could contain rows matching publication_year = 2007 — only p1 (since 2007 ∈ [2001, 2011)).

Distinction from range sharding¶

Range partitioning is the same-server sibling of range sharding:

Axis	Range partitioning	Range sharding
Physical unit	File on one server	Separate server / cluster
Machinery	MySQL-native, declarative	External framework (Vitess, CockroachDB, etc.)
Fault isolation	None — one server, one fate	Cross-server — one shard down ≠ all shards down
Scales	Vertically (faster disks, more cores)	Horizontally (more servers)
Hotspot mode	One-partition hotspot on monotonic IDs	Same — one-shard hotspot on monotonic IDs
Drift mitigation	`ALTER TABLE ... REORGANIZE PARTITION`	`reshard` workflow (Vitess)

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

Seen in¶

sources/2026-04-21-planetscale-what-is-mysql-partitioning — Brian Morrison II (PlanetScale, 2023-10-10) canonicalises the RANGE strategy on a library_books publication_year example with MINVALUE/MAXVALUE bracketing and a "scan only p1 for 2007" partition-pruning demo.