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))
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));