
* ALGORITHM=INSTANT - This was added in MariaDB 10.3.7.

See InnoDB Online DDL Overview: ALGORITHM for information on how the ALGORITHM
clause affects InnoDB.

ALGORITHM=DEFAULT
-----------------

The default behavior, which occurs if ALGORITHM=DEFAULT is specified, or if
ALGORITHM is not specified at all, usually only makes a copy if the operation
doesn't support being done in-place at all. In this case, the most efficient
available algorithm will usually be used.

However, in MariaDB 10.3.6 and before, if the value of the old_alter_table
system variable is set to ON, then the default behavior is to perform ALTER
TABLE operations by making a copy of the table using the old algorithm.

In MariaDB 10.3.7 and later, the old_alter_table system variable is
deprecated. Instead, the alter_algorithm system variable defines the default
algorithm for ALTER TABLE operations.

ALGORITHM=COPY
--------------

ALGORITHM=COPY is the name for the original ALTER TABLE algorithm from early
MariaDB versions.

When ALGORITHM=COPY is set, MariaDB essentially does the following operations:

-- Create a temporary table with the new definition
CREATE TEMPORARY TABLE tmp_tab (
...
);

-- Copy the data from the original table
INSERT INTO tmp_tab
 SELECT * FROM original_tab;

-- Drop the original table
DROP TABLE original_tab;

-- Rename the temporary table, so that it replaces the original one
RENAME TABLE tmp_tab TO original_tab;

This algorithm is very inefficient, but it is generic, so it works for all
storage engines.

If ALGORITHM=COPY is specified, then the copy algorithm will be used even if
it is not necessary. This can result in a lengthy table copy. If multiple
ALTER TABLE operations are required that each require the table to be rebuilt,
then it is best to specify all operations in a single ALTER TABLE statement,
so that the table is only rebuilt once.

ALGORITHM=INPLACE
-----------------

ALGORITHM=COPY can be incredibly slow, because the whole table has to be
copied and rebuilt. ALGORITHM=INPLACE was introduced as a way to avoid this by
performing operations in-place and avoiding the table copy and rebuild, when
possible.

When ALGORITHM=INPLACE is set, the underlying storage engine uses
optimizations to perform the operation while avoiding the table copy and
rebuild. However, INPLACE is a bit of a misnomer, since some operations may
still require the table to be rebuilt for some storage engines. Regardless,
several operations can be performed without a full copy of the table for some
storage engines.

A more accurate name would have been ALGORITHM=ENGINE, where ENGINE refers to
an "engine-specific" algorithm.

If an ALTER TABLE operation supports ALGORITHM=INPLACE, then it can be
performed using optimizations by the underlying storage engine, but it may
rebuilt.

See InnoDB Online DDL Operations with ALGORITHM=INPLACE for more.

ALGORITHM=NOCOPY
----------------

ALGORITHM=NOCOPY was introduced in MariaDB 10.3.7.

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has
to rebuild the clustered index, because when the clustered index has to be
rebuilt, the whole table has to be rebuilt. ALGORITHM=NOCOPY was introduced as
a way to avoid this.

If an ALTER TABLE operation supports ALGORITHM=NOCOPY, then it can be
performed without rebuilding the clustered index.

If ALGORITHM=NOCOPY is specified for an ALTER TABLE operation that does not
support ALGORITHM=NOCOPY, then an error will be raised. In this case, raising
an error is preferable, if the alternative is for the operation to rebuild the
clustered index, and perform unexpectedly slowly.

See InnoDB Online DDL Operations with ALGORITHM=NOCOPY for more.

ALGORITHM=INSTANT
-----------------

ALGORITHM=INSTANT was introduced in MariaDB 10.3.7.

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has
to modify data files. ALGORITHM=INSTANT was introduced as a way to avoid this.

If an ALTER TABLE operation supports ALGORITHM=INSTANT, then it can be
performed without modifying any data files.

If ALGORITHM=INSTANT is specified for an ALTER TABLE operation that does not
support ALGORITHM=INSTANT, then an error will be raised. In this case, raising
an error is preferable, if the alternative is for the operation to modify data
files, and perform unexpectedly slowly.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT for more.

LOCK
----

The ALTER TABLE statement supports the LOCK clause. This clause is one of the
clauses that is used to implement online DDL. ALTER TABLE supports several
different locking strategies. A locking strategy can be explicitly chosen for
an ALTER TABLE operation by setting the LOCK clause. The supported values are:

* DEFAULT: Acquire the least restrictive lock on the table that is supported
for the specific operation. Permit the maximum amount of concurrency that is
supported for the specific operation.
* NONE: Acquire no lock on the table. Permit all concurrent DML. If this
locking strategy is not permitted for an operation, then an error is raised.
* SHARED: Acquire a read lock on the table. Permit read-only concurrent DML.
If this locking strategy is not permitted for an operation, then an error is
raised.
* EXCLUSIVE: Acquire a write lock on the table. Do not permit concurrent DML.

Different storage engines support different locking strategies for different
operations. If a specific locking strategy is chosen for an ALTER TABLE