[SOLVED] Identify the partition of the hash partitioned table when inserting new data

Identify the partition of the hash partitioned table when inserting new data

I have created a new table and hash partition like this:

CREATE TABLE employees (
    id INT NOT NULL,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;`

Then I insert the value into the table:

INSERT INTO mydatabase.employees (id, store_id)  VALUES (1, 1)

How can I know which partition of the 4 partitions will be used to store the data without query on table?

Solution

HASH partitioning uses modulus, as a comment above says.

You don't need to query the table, you can calculate the modulus of the store_id value you're interested in by the number of partitions.

1 modulus 4 = 1

If you are not sure about this, you can verify this with EXPLAIN. This still doesn't execute the query against the table, it just reports the optimization plan.

mysql> explain select * from employees where store_id=1;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | p1         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

The column partitions in the EXPLAIN output shows that the query only needs to read partition p1, therefore that's the partition the rows with the store_id value 1 are in.

In MySQL 5.x, you need to use EXPLAIN PARTITIONS to get this column in the EXPLAIN output. In MySQL 8.0, it is included by default.