Partition Keys Can Make Unique Indexes Useless

  • by
Normally, we build an unique index for protecting the table from inserting duplicate values of combined columns. If the constraint of the unique index is always unique, extremely unique, it might become useless for your business logic. Worse, it might let some unwanted rows sneak into the dependent table without violations. Next, let me explain in what situation the unique indexes would become useless.

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

Leave a Reply

Your email address will not be published. Required fields are marked *