When MySQL 5.6 introduced the ALGORITHM attribute to ALTER TABLE, only two possible values were introduced in addition to the implied value ALGORITHM=DEFAULT: ALGORITHM=COPY to refer to the old-style ALTER TABLE that basically consists of CREATE TABLE, INSERT…SELECT, RENAME TABLE and DROP TABLE, and ALGORITHM=INPLACE to refer to the "fast" ALTER TABLE whose first incarnation was implemented in the InnoDB Plugin for MySQL 5.1.
This choice of keywords is unfortunate, because the supposedly "fast" algorithm would sometimes copy the table. Already the InnoDB Plugin would do it when executing ADD PRIMARY KEY. In MySQL 5.6, several ALGORITHM=INPLACE operations would actually copy the table. Most notably, these would be ADD COLUMN, DROP COLUMN, reordering columns, and changing ROW_FORMAT, and naturally the FORCE attribute that forces a rebuild.
The following was originally written in
MDEV-11369, which would make certain cases of ADD COLUMN an instantaneous operation:
It would be nice to introduce new syntax to prevent nasty surprises. When an operation is expected to be quick, it could be better to return an error than to perform a disruptive (resource-intensive and time-consuming) operation.
ALGORITHM=INSTANT will refuse any operation that must modify any data files.
ALGORITHM=NOCOPY will refuse any operation that would rebuild the clustered index (and the whole table).
With respect to the allowed operations, ALGORITHM=INSTANT is a subset of ALGORITHM=NOCOPY which is a subset of ALGORITHM=INPLACE which is a subset of ALGORITHM=COPY.