3

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.

1 Answer 1

5

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
;
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.