MySQL Online DDL: ALGORITHM INSTANT, INPLACE and COPY

On a small table, ALTER TABLE takes a blink. On a table with 100 million rows, it can take hours and, in the worst case, block writes the whole time. Online DDL is InnoDB’s way of making many changes faster and non-blocking.

The three algorithms

Algorithm	What it does	Speed
`INSTANT`	Changes only the table’s metadata. No rows are touched.	Instant, whatever the size
`INPLACE`	Rebuilds or edits the table in place, often while allowing reads and writes	Depends on size
`COPY`	Creates a new copy of the table, copies every row, swaps	Slowest, blocks writes

MySQL picks the fastest one that works. You can demand one. If the operation cannot be done that way, you get an error instead of a surprise.

query.sql

SQL

CREATE TABLE big_table (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);
INSERT INTO big_table (name) VALUES ('a'), ('b'), ('c');

ALTER TABLE big_table ADD COLUMN created_on DATE NULL, ALGORITHM = INSTANT;
SHOW COLUMNS FROM big_table;

Output

OUTPUT

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| name       | varchar(50) | NO   |     | NULL    |                |
| created_on | date        | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
3 rows in set

Adding a column is INSTANT, even at the end or in the middle of a table (MySQL 8.0.29 and later; before that only at the end).

Asking for something that cannot be instant

query.sql

SQL

ALTER TABLE big_table MODIFY name VARCHAR(10) NOT NULL, ALGORITHM = INSTANT;

Output

OUTPUT

ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.

Changing a column’s type needs a rebuild. Because you demanded INSTANT, MySQL refused. That is the point of naming the algorithm: you never start a surprise rebuild.

Choosing the lock too

LOCK says how much concurrent access you allow:

Lock	Meaning
`NONE`	Reads and writes continue
`SHARED`	Reads continue, writes wait
`EXCLUSIVE`	Everything waits

query.sql

SQL

1
2

ALTER TABLE big_table ADD INDEX idx_name (name), ALGORITHM = INPLACE, LOCK = NONE;
SHOW INDEX FROM big_table;

Output

OUTPUT

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| big_table |          0 | PRIMARY  |            1 | id          | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| big_table |          1 | idx_name |            1 | name        | A         |           3 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set

What is fast, roughly

Change	Typical behaviour
Add a column	`INSTANT`
Drop a column	`INSTANT` (8.0.29 and later)
Rename a column	`INSTANT`
Change a default	`INSTANT`
Add a secondary index	`INPLACE`, reads and writes continue
Change a column’s data type	`COPY` (rebuild, blocks writes)
Change the character set	`COPY`
Add a `FULLTEXT` index (first one)	`INPLACE` with some limits

Always read the table for your version in the MySQL manual under “Online DDL Operations”. It changes between releases, and it is the reliable reference.

Practical advice

Always state ALGORITHM and LOCK when you change a large production table. If MySQL refuses, you learn it in a second, not after a two-hour lock.
Test on a copy of production size.
For changes that need a full rebuild on a huge table, use a tool built for it, such as pt-online-schema-change or gh-ost, or schedule downtime.
Watch disk space and replication lag during a rebuild.
Watch for the metadata lock: an ALTER waits for open transactions on the table and then blocks everything queued behind it. Long-running transactions are the usual cause of an ALTER that “hangs”.

lightbulb

Habit for big tables

Write ALTER TABLE statements with ALGORITHM=INSTANT or ALGORITHM=INPLACE, LOCK=NONE while developing. If it works, you know it is cheap. If it fails, you know before production does.