In partitioning, we must add partition key into every existent unique indexes for complying with the rules of partitioning in MySQL. Let's say the partition key A is an auto increment column which is already unique and to be added to another unique index composed by BC columns, you may find out the unique index become:
A (unique) + BC (unique) = ABC (absolutely unique)The latter one, a very positively unique, never being violated unique index ABC can never prevent BC combined duplicate values from inserting into the table. This kinds of index have not any constraint imposed on rows at all and become useless for business logic. Which means, one unique index combined (AND) another unique index produces a nonsense, valueless unique index.
So what should we do to keep the unique indexes meaningful and useful for partition tables? The solution is that reconsidering another partition key came from the members of the existent unique indexes if possible.
If you're unable to select the right partition key, then don't have the table partitioned. Try some workarounds:
How to Accelerate Queries on Big Tables