Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
