RANGE Partitioning Type
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) );