CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT ’1970-01-01’, separated DATE NOT NULL DEFAULT ’9999-12-31’, job_code INT, store_id INT ) PARTITION BY HASH( YEAR(hired) ) PARTITIONS 4; 

expr  must return a nonconstant, nonrandom integer value (in other words, it should be varying but deterministic), and must not contain any prohibited constructs as described in Section 24.6, “Restrictions and Limitations on Partitioning” . You should also keep in mind that this expression is evaluated each time a row is inserted or updated (or possibly deleted); this means that very complex expressions may give rise to performance issues, particularly when performing operations (such as batch inserts) that affect a great many rows at one time. 

The most efficient hashing function is one which operates upon a single table column and whose value increases or decreases consistently with the column value, as this allows for “pruning”  on ranges of partitions. That is, the more closely that the expression varies with the value of the column on which it is based, the more efficiently MySQL can use the expression for hash partitioning. 

For example, where date_col  is a column of type DATE , then the expression TO_DAYS(date_col) is said to vary directly with the value of date_col , because for every change in the value of date_col , the value of the expression changes in a consistent manner. The variance of the expression YEAR(date_col)  with respect to date_col  is not quite as direct as that of TO_DAYS(date_col) , because not every possible change in date_col  produces an equivalent change in YEAR(date_col) . Even so, YEAR(date_col)  is a good candidate for a hashing function, because it varies directly with a portion of date_col  and there is no possible change in date_col  that produces a disproportionate change in YEAR(date_col) . 

By way of contrast, suppose that you have a column named int_col  whose type is INT . Now consider the expression POW(5-int_col,3) + 6 . This would be a poor choice for a hashing function because a change in the value of int_col  is not guaranteed to produce a proportional change in the value of the expression. Changing the value of int_col  by a given amount can produce widely differing changes in the value of the expression. For example, changing int_col  from 5  to 6  produces a change of -1  in the value of the expression, but changing the value of int_col  from 6  to 7  produces a change of -7  in the expression value. 

In other words, the more closely the graph of the column value versus the value of the expression follows a straight line as traced by the equation y= c x  where c  is some nonzero constant, the better the expression is suited to hashing. This has to do with the fact that the more nonlinear an expression is, the more uneven the distribution of data among the partitions it tends to produce. 

In theory, pruning is also possible for expressions involving more than one column value, but determining which of such expressions are suitable can be quite difficult and time-consuming. For this reason, the use of hashing expressions involving multiple columns is not particularly recommended. 

When PARTITION BY HASH  is used, the storage engine determines which partition of num  partitions to use based on the modulus of the result of the expression. In other words, for a given expression expr , the partition in which the record is stored is partition number N , where N  = MOD( expr , num ) . Suppose that table t1  is defined as follows, so that it has 4 partitions: 

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4; 

