I just read this MySQL 8 official docs for new features of online DDL operation, where new algorithms added INSTANT. but I am a little bit confused about its working. Can someone please help me to understand the below statement

The benefit of the INSTANT algorithm is that only metadata changes are made in the data dictionary. There is no need to acquire metadata lock during SE changes and we don’t touch the data of the table.

  • in short prior you have to rebuild teh hole Table , which on realy big tables take forever. and UNSTANT helps with that problem see mysqlserverteam.com/…
    – nbk
    Jun 21, 2020 at 9:48
  • Here my doubt is about line "INSTANT algorithm is that only metadata changes are made in the data dictionary." so how while doing meta data change , concurrent DML is allowed ? and how its different from INPLACE algorithm ? Jun 21, 2020 at 15:28
  • I linked the article, so that you get the general idea bhind it and can ask specific questions that are not covered. the examples are comprehenisive and you can try ot on your own.
    – nbk
    Jun 21, 2020 at 15:47

1 Answer 1


The manual goes into a lot of gory details; you can mostly ignore them. In general, if you do not provide an "algorithm", the ALTER will pick the fastest way. If you specify INSTANT, but that algorithm is not possible for what you want to change, it will spit at you. (No harm done.)

At one extreme (the COPY end): Changing the PRIMARY KEY (on InnoDB) requires rebuilding the entire table, reordering the data, and rebuilding all the indexes (because the PK is part of every secondary index).

At the other extreme (the INSTANT end): Adding an option to the end of an ENUM list does not require touching the data at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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