How values get inserted into Mysql hash partitioned table?

Question

I have created a mysql table and hash partitioned it as below.

      mysql> 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,
               PRIMARY KEY(id)
             )
             PARTITION BY HASH(id)
             PARTITIONS 10;

After I created table successfully, I inserted value 1(into store_id) into the table shown below

  mysql>INSERT INTO employees (store_id) values (1);

Now I don't understand where will this value of 1 go into? Into which partition (p0,p1,p2......p10) store_id value 1 go? I thought it would go into p0. but it did not. see below I checked it like this

  mysql>SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH,DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME LIKE 'employees';

it has shown the value went into p1.see below

   mysql>
        +------------+----------------+------------+----------------+-------------+
        | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
        +------------+----------------+------------+----------------+-------------+
        | employees  | p0             |          0 |              0 |       16384 |
        | employees  | p1             |          1 |          16384 |       16384 |
        | employees  | p2             |          0 |              0 |       16384 |
        | employees  | p3             |          0 |              0 |       16384 |
        | employees  | p4             |          0 |              0 |       16384 |
        | employees  | p5             |          0 |              0 |       16384 |
        | employees  | p6             |          0 |              0 |       16384 |
        | employees  | p7             |          0 |              0 |       16384 |
        | employees  | p8             |          0 |              0 |       16384 |
        | employees  | p9             |          0 |              0 |       16384 |
        +------------+----------------+------------+----------------+-------------+

I don'tknow why it got inserted into p1.tested it again.. I inserted value 2 this time...

       mysql> INSERT INTO employees (store_id) values (2);

It has got entered into p2.

         +------------+----------------+------------+----------------+-------------+
         | TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
         +------------+----------------+------------+----------------+-------------+
         | employees  | p0             |          0 |              0 |       16384 |
         | employees  | p1             |          1 |          16384 |       16384 |
         | employees  | p2             |          1 |          16384 |       16384 |
         | employees  | p3             |          0 |              0 |       16384 |
         | employees  | p4             |          0 |              0 |       16384 |
         | employees  | p5             |          0 |              0 |       16384 |
         | employees  | p6             |          0 |              0 |       16384 |
         | employees  | p7             |          0 |              0 |       16384 |
         | employees  | p8             |          0 |              0 |       16384 |
         | employees  | p9             |          0 |              0 |       16384 |
         +------------+----------------+------------+----------------+-------------+

why values are getting inserted into different partitions? Is there any rule that hash partition follow? Interestingly it left p0 and started getting inserted into p1? Explain?

`PARTITION BY HASH` provide no performance benefit that I know of. What are you hoping for? (Jon's answer addresses your question nicely; I am pointing out that `HASH` may be useless anyway.) — Rick James, Nov 27 '15 at 18:15

score 1 · Accepted Answer · answered Nov 06 '15 at 13:21

1

If this explanation holds true for your MySQL version the partition number is found this way: MOD([Your input],[Number of partitions]).

In your case the first row probably has id = 1 and the calculation will be MOD(1,10) = 1. The row goes to partition 1 (id= 2 goes to partition 2).

answered Nov 06 '15 at 13:21

Jon Tofte-Hansen

794
6
16

Thank you for the answer. So in hash partitioning we cannot certainly say inserted values distribute equally among all the partitions. rite? – Nov 06 '15 at 14:24
http://www.w3resource.com/mysql/mathematical-functions/mysql-mod-function.php In this link it says first argument of MOD is Dividend and next is Divisor. what happens if the dividend is less than the divisor? Example MOD(2,7)? how will it select the partition among all partitions to store the value? Thanks in advance :-) – Nov 06 '15 at 14:33
http://stackoverflow.com/questions/3622298/how-does-modulus-of-a-smaller-dividend-and-larger-divisor-work I figured that answer here . Thank you :-) – Nov 06 '15 at 14:36
Concerning even distribution: You control it yourself. If the `id` is only 1,11,21,31,41 etc. only partition 1 is filled. But that would probably rarely be the case for an id-column. – Jon Tofte-Hansen Nov 06 '15 at 15:15

How values get inserted into Mysql hash partitioned table?

1 Answers1