Re: Partition by Key

[Mattias Jonsson]

What you probably want to use is RANGE partitioning. With 'PARTITION BY RANGE (col)' you can specify how to partition your data, KEY and HASH partitioning is more like automatic partitioning, and you cannot decide which specific partition to drop, you can only increase or decrease the number of partitions (ADD/COALESCE PARTITION) which will redistribute your data between the new number of partitions.

Then I wonder, why do you have AUTO_INCREMENT on the pyear column? (seems very strange...)

The easiest way to see which partition contains that row is to look at INFORMATION_SCHEMA.PARTITIONS (at least if it the only row in the table, that is exact for MyISAM but not for InnoDB).