Making an index a unique index in very large MySQL table within one transaction - is the following approach safe?

Question

For the purpose of optimizing SELECT statements I am trying to make an index UNIQUE with the following SQL statement in MySQL:

ALTER TABLE credentials DROP INDEX special_credential_id, ADD UNIQUE KEY special_credential_id(special_credential_id)

My question is: Is this one transaction? That means if creating the unique index fails, will the old special_credential_id index still be there? Usually it would be easy simply to create a new index but we are talking about a table containing 100 Mio entries.

Answer 1

That is one transaction. It should work as is but please follow these two steps:

STEP 1

Before doing this, test the uniqueness. Run this count query:

SELECT special_credential_id,COUNT(1) cnt FROM credentials 
GROUP BY special_credential_id HAVING COUNT(1) > 1;

If any rows come back (i.e., not "Empty Set"), fix the keys so thaty they are unique. Otherwise, proceed

STEP 2

I would perform this as Online DDL

ALTER TABLE credentials
    DROP INDEX special_credential_id
   ,ADD UNIQUE KEY special_credential_id (special_credential_id)
   ,ALGORITHM=INPLACE
   ,LOCK=NONE
;

If you are concerned about this not working, do it as two steps

ALTER TABLE credentials
    ADD UNIQUE KEY special_credential_id_2 (special_credential_id)
   ,ALGORITHM=INPLACE
   ,LOCK=NONE
;
ALTER TABLE credentials
    DROP INDEX special_credential_id
   ,ALGORITHM=INPLACE
   ,LOCK=NONE
;