•Some queries can be greatly optimized in virtue of the fact that data satisfying a given WHERE  clause can be stored only on one or more partitions, which automatically excludes any remaining partitions from the search. Because partitions can be altered after a partitioned table has been created, you can reorganize your data to enhance frequent queries that may not have been often used when the partitioning scheme was first set up. This ability to exclude non-matching partitions (and thus any rows they contain) is often referred to as partition pruning . For more information, see Section 24.4, “Partition Pruning” . 

In addition, MySQL supports explicit partition selection for queries. For example, SELECT * FROM t PARTITION (p0,p1) WHERE c < 5  selects only those rows in partitions p0  and p1  that match the WHERE  condition. In this case, MySQL does not check any other partitions of table t ; this can greatly speed up queries when you already know which partition or partitions you wish to examine. Partition selection is also supported for the data modification statements DELETE , INSERT , REPLACE , UPDATE , and LOAD DATA , LOAD XML . See the descriptions of these statements for more information and examples. 

## 24.2 Partitioning Types 

This section discusses the types of partitioning which are available in MySQL 8.0. These include the types listed here: 

• RANGE partitioning.  This type of partitioning assigns rows to partitions based on column values falling within a given range. See Section 24.2.1, “RANGE Partitioning” . For information about an extension to this type, RANGE COLUMNS , see Section 24.2.3.1, “RANGE COLUMNS partitioning” . 

• LIST partitioning.  Similar to partitioning by RANGE , except that the partition is selected based on columns matching one of a set of discrete values. See Section 24.2.2, “LIST Partitioning” . For information about an extension to this type, LIST COLUMNS , see Section 24.2.3.2, “LIST COLUMNS partitioning” . 

• HASH partitioning.  With this type of partitioning, a partition is selected based on the value returned by a user-defined expression that operates on column values in rows to be inserted into the table. The function may consist of any expression valid in MySQL that yields a nonnegative integer value. An extension to this type, LINEAR HASH , is also available. See Section 24.2.4, “HASH Partitioning” . 

• KEY partitioning.  This type of partitioning is similar to partitioning by HASH , except that only one or more columns to be evaluated are supplied, and the MySQL server provides its own hashing function. These columns can contain other than integer values, since the hashing function supplied by MySQL guarantees an integer result regardless of the column data type. An extension to this type, LINEAR KEY , is also available. See Section 24.2.5, “KEY Partitioning” . 

A very common use of database partitioning is to segregate data by date. Some database systems support explicit date partitioning, which MySQL does not implement in 8.0. However, it is not difficult in MySQL to create partitioning schemes based on DATE , TIME , or DATETIME  columns, or based on expressions making use of such columns. 

When partitioning by KEY  or LINEAR KEY , you can use a DATE , TIME , or DATETIME  column as the partitioning column without performing any modification of the column value. For example, this table creation statement is perfectly valid in MySQL: 

CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL ) 

