castanyes blaves

LINEAR HASH partitioning -> This is almost similar to hash partitioning except for the fact that the algorithm used to divide data is different. The syntax is also almost same. We use PARTITION BY LINEAR HASH instead of PARTITION BY HASH.

The algorithm used is :

Given an expression expr, the partition in which the record is stored when linear hashing is used is partition number N from among num partitions, where N is derived according to the following algorithm:


* Find the next power of 2 greater than num. We call this value V; it can be calculated as:
V = POWER(2, CEILING(LOG(2, num)))

* Set N = F(column_list) & (V - 1).

* While N >= num
{
Set V = CEIL(V / 2)
Set N = N & (V - 1)
}



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 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.

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. The syntax used is PARTITION BY KEY instead of PARTITION BY HASH.

In most of the cases either a primary key or an unique key is used to create partitions.

Mysql also supports sub partitioning whereby a partition can be divided into further sub partitions of similar type.