The RANGE partitioning type assigns a different range of values to each partition. For each range, we only specify a higher bound. The first range starts with NULL, which is the lowest possible value. The other ranges start with the value that immediately follows the higher bound of the previous partition.

Consider the following example:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, date)
)
  ENGINE = InnoDB
PARTITION BY RANGE (YEAR(date)) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (2010),
  PARTITION current VALUES LESS THAN (2020)
);

The last partition holds values that are less than 2020. This will not be a problem for some years (the current year is 2014), but some day it may be problem. If we try to insert a row for which the partitioning expression returns an out-of-range value, we will get an error like the following:

ERROR 1292 (22007): Incorrect date value: '2020' for column 'date' at row 1

The error can be suppressed with the IGNORE clause, but the row will not be inserted.

However, the following syntax can be used to allow partition values that are too high to be stored in the previous partitions:

PARTITION p_name VALUES LESS THAN (MAXVALUE)

The parenthesis here are optional.

In the preceding example, we have a table that contains articles. The table is very large and we want to partition it to process common queries faster. Since the table contains very old articles, RANGE seems like a good partitioning type to use for the following reasons:

The LIST partitioning type is quite similar to RANGE though LIST assigns to each partition a list of values rather than a range. Each partition must be assigned at least one value. The order of partitions is not relevant for the LIST type. All possible values must be explicitly names; there is no way to put all unmentioned values into a partition, which is similar to what is done with MAXVALUE for the RANGE type. When trying to insert a value that is not assigned to a partition, we get an error shown as follows:

Error (Code 1526): Table has no partition for value 100

The following example shows how to create a LIST partitioned table:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, language)
)
  ENGINE = InnoDB
PARTITION BY LIST (language) (
  PARTITION p0 VALUES IN (1),
  PARTITION p1 VALUES IN (2, 3),
  PARTITION p2 VALUES IN (4, 5, 6, 7, 8, 9, 10, 11),
  PARTITION px VALUES IN (NULL)
);

In this example, language acts a foreign key to a table that stores information about languages (probably just an incremental ID and their name). The reason why the foreign key has not been declared is that foreign keys are not supported for partitioned tables.

Here, the partitioning expression is a simple column name. This works because language is an integer column that only contains positive values. Of course, a CHAR column could be used too, for example, to store the language's ISO code; however, in that case, the expression should translate its string value into a number.

Despite the language column being an integer, the values cannot be sorted as per logic, thus splitting them into ranges would make no sense. There are several reasons why creating a LIST partitioned table based on the language can be a good choice:

The RANGE and LIST partitioning types have two variations called RANGE COLUMNS and LIST COLUMNS. When they are used, there is no single partitioning expression: the values are assigned to partitions based on a list of columns. This list is sometimes called a partitioned columns list in the documentation and in technical articles. No functions, operators, or any kind of elaboration is allowed to transform the columns values. However, for COLUMNS partitioning types, more data types are allowed:

This is very important because as the use of functions is not allowed, there is no way to convert a noninteger value into an integer.

Note that the example used for the LIST type could be a LIST COLUMNS type too because it only included a column name.

The RANGE COLUMNS type is primarily useful when the range of values need to be based on multiple columns to obtain a good distribution of values. The following example shows the syntax to be used:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  year CHAR(4) NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, language, year)
)
  ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (language, year) (
  -- old english articles
  PARTITION p0 VALUES LESS THAN (1, '2010'),
  -- recent english articles
  PARTITION p1 VALUES LESS THAN (1, '2020'),
  -- old non-english articles
  PARTITION p2 VALUES LESS THAN (100, '2010'),
  -- recent non-english articles
  PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

This partitioning strategy reflects a very common situation:

Note that we cannot use YEAR(date), so we choose to duplicate the year information into a string column. With all characters being digits, the order will not change, provided that all values consist of four characters.

The LIST COLUMNS type is useful to match a list of values against a noninteger column. Let's go back to the example used for the LIST type. We store languages in the form of integer values, but maybe it's preferable to store a two-character ISO code. While this is not possible with the LIST type, LIST COLUMNS allows us to do this:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, language)
)
  ENGINE = InnoDB
PARTITION BY LIST (language) (
  PARTITION p0 VALUES IN ('en'),
  PARTITION p1 VALUES IN ('fr', 'de'),
  
PARTITION p2 VALUES IN ('es', 'it', 'ir', 'is', 'nl', 'ru', 'ro', 'sr'),
  PARTITION px VALUES IN (NULL)
);

The HASH and KEY partitioning types are similar. Their purpose is to provide a more uniform row distribution among partitions. Each different value returned by the partitioning expression has equal probabilities to be assigned to each partition. A group of contiguous values will be assigned to different partitions. However, if the distribution of unique values has very high peaks (that is, a limited set of values occur very often), it is possible that some partitions will sensibly contain more data than others. The KEY and HASH types work better if the distribution of unique values is uniform.

With HASH and KEY, the target partition for new rows is determined automatically by the server. It uses the following formula:

target_partition = expression_result MOD number__of_partitions

In the previous syntax, MOD is the modulus operator that returns the rest of a division.

The choice between HASH and KEY depends on the partitioning function that we want to use. The difference between these types is similar to the difference between LIST and LIST COLUMNS, which makes it easier to remember.

With the KEY type, a hash of the return value is calculated. The hash uses the PASSWORD() function, which uses a variation of an SHA algorithm.

Usually, the following syntax is used to create a table partitioned by HASH:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, date)
)
  ENGINE = InnoDB
PARTITION BY HASH(YEAR(date))
PARTITIONS 8;

In fact, most of the time we only want to determine the partitioning expression and the number of partitions. In this case, we have chosen YEAR(date) as the partitioning expression so that all articles published in the same year will be in the same partition. This will allow most queries to access only one partition.

The following syntax is also allowed:

…
PARTITION BY HASH(MONTH(date)) (
    PARTITION p0,
    PARTITION p1
);

This allows us to specify a name for each partition. A path for each partition's files can also be specified, as explained later in this chapter in the Partitions' physical files section.

To create a table partitioned by KEY, use the following code:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, date)
)
  ENGINE = InnoDB
PARTITION BY KEY(id)
PARTITIONS 8;

In this example, we partitioned the table with an AUTO_INCREMENT key, which will provide a distribution of the most uniform values.

Again, we can specify a name for each partition, like we did in the HASH example.

Otherwise, we can use an even more concise syntax. Since id is the primary key, it need not be explicitly named for KEY partitioning:

…
PARTITION BY KEY()
PARTITIONS 8;

The LINEAR HASH and LINEAR KEY partitioning types are identical to HASH and KEY, except that a much more complicated algorithm is used to select new rows to target partitions. The linear formula is less efficient for normal database operations, such as row reads and writes. However, it makes some administering operations much faster. If the performance of partition creation, drop, split, and merge are a problem, using the LINEAR keyword can be a solution.

Main partitions can be split into multiple subpartitions. Only one subpartitioning level is supported, which means that subpartitions cannot be further split. Each partition must have the same number of subpartitions.

A table with subpartitions can only be partitioned by RANGE or LIST and subpartitioned by HASH or KEY. The COLUMNS and LINEAR keywords are allowed. Using any other combination of types produces the following error:

ERROR 1500 (HY000): It is only possible to mix RANGE/LIST partitioning with HASH/KEY partitioning for subpartitioning

The syntax to create a subpartitioned table is as follows:

CREATE TABLE article (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  date DATE NOT NULL,
  author VARCHAR(100),
  language TINYINT UNSIGNED,
  text TEXT,
  PRIMARY KEY (id, date)
)
  ENGINE = InnoDB
PARTITION BY RANGE COLUMNS (date)
SUBPARTITION BY LINEAR HASH (id) (
  PARTITION p0 VALUES LESS THAN ('2000-01-01') (
    SUBPARTITION s0,
    SUBPARTITION s1
  ),
  PARTITION p1 VALUES LESS THAN ('2020-01-01') (
    SUBPARTITION s2,
    SUBPARTITION s3
  )
);

Note that each subpartition must have a unique name within the table. Each partition can be stored in a different path, as explained in the Partitions' physical files section.

A concise syntax can also be used, if we do not need to specify names or paths for subpartitions:

…
PARTITION BY RANGE COLUMNS (date)
SUBPARTITION BY LINEAR KEY (id)
SUBPARTITIONS 2 (
  PARTITION p0 VALUES LESS THAN ('2000-01-01'),
  PARTITION p1 VALUES LESS THAN ('2020-01-01')
);