What happens to the hidden MySQL [InnoDB] PK when you add a PK?

Question

When using the InnoDB storage engine in MySQL if you do not specify a PRIMARY KEY when creating a table than a hidden unique index is used as the clustered index. Due to a mutex lock on the data dictionary, I've read that these hidden indexes can cause contention.

My question is - if I add a PRIMARY KEY to a table, what happens to the hidden PK?

Answer 1

If you add a PRIMARY KEY to a table, InnoDB uses that as the cluster index.

If InnoDB was using a UNIQUE INDEX on non-NULLable columns as the cluster index, the UNIQUE INDEX will still exist, but it will become a secondary index (when you add the PRIMARY KEY.)

If InnoDB was using a hidden index on the synthetic row id column as the cluster index, then that hidden index will be removed (when you add the PRIMARY KEY).