The other day I was running pt-duplicate-key-checker on behalf of a customer and noticed some peculiar recommendations on an InnoDB table with an odd structure (no PRIMARY key, but multiple UNIQUE constraints). This got me thinking about how InnoDB promotes UNIQUE constraints to the role of PRIMARY KEYs. The documentation is pretty clear:
[DOCS]
When you define a PRIMARY KEY on your table, InnoDB uses it as
the clustered index. Define a primary key for each table that you
create. If there is no logical unique and non-null column or set
of columns, add a new auto-increment column, whose values are
filled in automatically.
If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key …
[Read more]