MySql - find in which partition a row is stored

Question

Is there a way to find out in which partition a particular row is stored?

I've created a table partitioned by HASH(YEAR(date)) and would like to find out which partition rows from a particular date are stored.

It looks like it's not possible to obtain this information by running:

set @partition_name=HASH(YEAR(date))

Answer 1

You can simply do this:

EXPLAIN SELECT * FROM some_table WHERE date = '2017-07-20'

You will get the following information about the rows in some_table having today's date: id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, Extra

You can also check the MySQL documentation for this from here

Answer 2

This question's old now, but it was the one that came up when I searched.

I think this should work.

MySql calculates the modulus of the value you use as the hash to the number of partitions for your table, as described in their docs.

For example, if your table is called sales and has 50 partitions, a row with the date 2017-07-20 will live in the partition with this name:

select partition_name from information_schema.partitions 
where table_name = 'sales' 
and partition_ordinal_position = 1+ abs(mod(year('2017-07-20'), 50));