RANGE Partitioning Type

You are viewing an old version of this article. View the current version here.

This article is currently incomplete

The RANGE partitioning type is used to assign each partition a range of values generated by the partitioning expression. Ranges must be ordered, contigous and non-overlapping. The minimum value is always included in the first range. The highest value may or may not be included in the last range.

Syntax

Use cases

A typical use case is when we want to partition a table whose rows refer to a moment or period in time; for example commercial transactions, blog posts, or events of some kind. We can partition the table by year, to keep all recent data in one partition and distribute historical data in big partitions that are stored on slower disks. Or, if our queries always read rows which refer to the same month or week, we can partition the table by month or year week (in this case, historical data and recent data will be stored together).

AUTO_INCREMENT values also represent a chronological order. So, these values can be used to store old data in separate partitions. However, partitioning by id is not the best choice if we usually query a table by date.

Example

In the following example, we will partition a log table by year.

CREATE TABLE log
(
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	timestamp DATETIME NOT NULL,
	user INT UNSIGNED,
	ip BINARY(16) NOT NULL,
	action VARCHAR(20) NOT NULL,
	PRIMARY KEY (id, timestamp)
)
	ENGINE = InnoDB
PARTITION BY RANGE (YEAR(timestamp))
(
	PARTITION p0 VALUES LESS THAN (2013),
	PARTITION p1 VALUES LESS THAN (2014),
	PARTITION p2 VALUES LESS THAN (2015),
	PARTITION p3 VALUES LESS THAN (2016)
);

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.