RANGE Partitioning

What is RANGE Partitioning?

RANGE partitioning is one of the most commonly used partitioning strategies in MySQL, especially for time-series or numerically ordered data. In this method, rows are distributed across partitions based on whether the value of a specific column falls within a defined numeric or date range.

You create partitions by specifying upper boundary values for each range, and MySQL automatically places each row into the correct partition. This makes RANGE partitioning ideal for datasets where the key column increases logically over time (such as years, months, or IDs).

The main advantage is that it keeps related data grouped together while allowing older or irrelevant ranges to be removed easily without affecting the rest of the table. It also significantly improves performance for range-based queries because MySQL can skip entire partitions using partition pruning.

Implementing RANGE Partitioning in MySQL

CREATE TABLE student_records (
   student_id INT,
   student_name VARCHAR(50),
   admission_year INT,
   department VARCHAR(50)
)
PARTITION BY RANGE (admission_year) (
   PARTITION p2019 VALUES LESS THAN (2020),
   PARTITION p2020 VALUES LESS THAN (2021),
   PARTITION p2021 VALUES LESS THAN (2022),
   PARTITION pmax VALUES LESS THAN MAXVALUE
);

How this works:

Rows with admission_year = 2019 go to p2019
Rows with admission_year = 2020 go to p2020
Rows with admission_year = 2021 go to p2021
Any year ≥ 2022 goes to the pmax partition

This setup ensures clean separation of data by year while maintaining the ability to scale easily.

Use Cases for RANGE Partitioning

RANGE partitioning is especially useful when your selection or maintenance patterns follow logical ranges. Common scenarios include:

1. Time-based Data
Annual, monthly, or daily logs
Website analytics
Sensor readings
Order histories

You can quickly drop old partitions to purge outdated logs without touching current data.

2. Financial Ranges
Salary bands
Tax brackets
Sales revenue tiers

Queries focused on a specific range automatically scan fewer partitions.

3. Archival Systems
If older data is archived regularly, RANGE partitioning allows you to simply drop old partitions instead of deleting millions of rows manually.