Range Partitioning in MySQL: What It Is and How It Works

In MySQL, range partitioning divides a table into parts based on ranges of column values, like splitting data by date or number intervals. Each partition holds rows where the column values fall within a specific range, making data management and queries faster for large datasets.

How It Works

Range partitioning in MySQL works by splitting a table into smaller pieces called partitions. Each partition contains rows where the value of a chosen column falls within a specific range. Imagine sorting your mail into different bins based on zip codes: all mail with zip codes from 10000 to 19999 goes in one bin, 20000 to 29999 in another, and so on. This way, when you want to find mail for a certain zip code, you only look in one bin instead of all mail.

In MySQL, you define these ranges when creating the table. The database then automatically stores rows in the correct partition based on the column value. This helps speed up queries that filter by that column because MySQL can skip partitions that don’t match the query range.

Example

This example shows how to create a table partitioned by ranges of a year column. Each partition holds data for a specific year range.

sql

CREATE TABLE sales (
  id INT,
  sale_year INT,
  amount DECIMAL(10,2)
)
PARTITION BY RANGE (sale_year) (
  PARTITION p0 VALUES LESS THAN (2010),
  PARTITION p1 VALUES LESS THAN (2015),
  PARTITION p2 VALUES LESS THAN (2020),
  PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO sales VALUES (1, 2008, 100.00), (2, 2012, 150.00), (3, 2018, 200.00), (4, 2021, 250.00);

SELECT * FROM sales WHERE sale_year < 2015;

Output

id | sale_year | amount
---|-----------|--------
1  | 2008      | 100.00
2  | 2012      | 150.00

When to Use

Use range partitioning when your data naturally divides into ranges, such as dates, years, or numeric intervals. It is especially helpful for very large tables where queries often filter by these ranges. For example, a sales database might partition data by year to speed up reports for recent years without scanning older data.

It also helps with maintenance tasks like archiving or deleting old data by dropping entire partitions instead of deleting rows one by one.

Key Points

• Range partitioning splits data into partitions based on value ranges of a column.
• It improves query speed by limiting data scanned to relevant partitions.
• It simplifies data management for large datasets with natural range divisions.
• Partitions are defined using VALUES LESS THAN clauses.
• Useful for time-based data like years, months, or numeric ranges.

Key Takeaways

Range partitioning divides a table into parts based on column value ranges.

It speeds up queries by scanning only relevant partitions.

Ideal for large tables with natural range-based data like dates or numbers.

Partitions are defined with VALUES LESS THAN in the table definition.

Helps with easier data maintenance like archiving or deleting old data.