V  = POWER(2, CEILING( LOG(2,6) )) = 8 N  = YEAR(’2003-04-14’) & (8 - 1) = 2003 & 7 = 3 ( 3 >= 6 is FALSE: record stored in partition #3 ) 

The number of the partition where the second record is stored is calculated as shown here: 

V  = 8 N  = YEAR(’1998-10-19’) & (8 - 1) = 1998 & 7 = 6 ( 6 >= 6 is TRUE: additional step required ) N  = 6 & ((8 / 2) - 1) = 6 & 3 = 2 ( 2 >= 6 is FALSE: record stored in partition #2 ) 

The advantage in partitioning by linear hash is that the adding, dropping, merging, and splitting of partitions is made much faster, which can be beneficial when dealing with tables containing extremely large amounts (terabytes) of data. The disadvantage is that data is less likely to be evenly distributed between partitions as compared with the distribution obtained using regular hash partitioning. 

## 24.2.5 KEY Partitioning 

Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user- defined expression, the hashing function for key partitioning is supplied by the MySQL server. NDB Cluster uses MD5()  for this purpose; for tables using other storage engines, the server employs its own internal hashing function. 

The syntax rules for CREATE TABLE ... PARTITION BY KEY  are similar to those for creating a table that is partitioned by hash. The major differences are listed here: 

• KEY  is used rather than HASH . 

• KEY  takes only a list of zero or more column names. Any columns used as the partitioning key must comprise part or all of the table’s primary key, if the table has one. Where no column name is specified as the partitioning key, the table’s primary key is used, if there is one. For example, the following CREATE TABLE  statement is valid in MySQL 8.0: 

CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) ) PARTITION BY KEY() PARTITIONS 2; 

If there is no primary key but there is a unique key, then the unique key is used for the partitioning key: 

CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id) ) PARTITION BY KEY() PARTITIONS 2; 

