Does adding an index lock the table in MySQL?

Short answer

No, not for reads and writes. On MySQL 8.4, adding an ordinary secondary index runs in place with LOCK=NONE: InnoDB reads the table, sorts the keys and builds the index while other sessions keep inserting, updating and deleting. Changes made during the build are logged and applied to the new index before it finishes. The table isn’t copied.

Two things can still hurt:

• It needs an exclusive metadata lock for a moment at the start and again at the end. If a transaction is open on the table at either moment, the ALTER waits, and every query that arrives after it waits too.
• A FULLTEXT or SPATIAL index can’t be built with LOCK=NONE; writes are blocked for the whole build.

Ask for the online behaviour explicitly, so MySQL refuses rather than locking more than you expect:

ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
-- or
CREATE INDEX idx_customer ON orders (customer_id) ALGORITHM=INPLACE LOCK=NONE;

What it locks

During the build, the ALTER holds a SHARED_UPGRADABLE metadata lock, which blocks other DDL on the table but not reads or writes. Reading performance_schema.metadata_locks while it ran:

+-------------+-------------------+-------------+----------------------------------------------------+
| OBJECT_NAME | LOCK_TYPE         | LOCK_STATUS | SQL_TEXT                                           |
+-------------+-------------------+-------------+----------------------------------------------------+
| orders      | SHARED_UPGRADABLE | GRANTED     | ALTER TABLE orders ADD INDEX idx_customer (custome |
+-------------+-------------------+-------------+----------------------------------------------------+

An UPDATE and an INSERT sent mid-build returned in 0.00 and 0.01 seconds while the ALTER (0.45 seconds that run) carried on, and the inserted row was in the new index afterwards. SHOW PROCESSLIST shows the build as altering table.

The exclusive moments are where it waits. If a transaction that touched the table is still open when the ALTER starts, it waits (Waiting for table metadata lock), the same as ADD COLUMN. Less obviously, the same happens at the end: a transaction that starts reading the table during the build and stays open makes the finished ALTER wait to commit, and new queries queue behind it. In our test, the transaction began 0.3 seconds into the build and stayed open for 5 seconds:

+-------+---------+------+---------------------------------+------------------------------------------------------------------------+
| ID    | COMMAND | TIME | STATE                           | INFO                                                                   |
+-------+---------+------+---------------------------------+------------------------------------------------------------------------+
| 30609 | Query   |    3 | Waiting for table metadata lock | ALTER TABLE seo_mysqlmig.orders ADD INDEX idx_status_created (status,  |
| 30610 | Sleep   |    3 |                                 | NULL                                                                   |
| 30613 | Query   |    0 | Waiting for table metadata lock | SELECT id, total FROM seo_mysqlmig.orders WHERE id = 2                 |
+-------+---------+------+---------------------------------+------------------------------------------------------------------------+

The ALTER finished after 5.27 seconds, once the transaction committed, and the one-row SELECT behind it took 2.47 seconds.

Does it rewrite the table?

No. A secondary index is a separate structure; the table’s rows stay where they are. The build does scan the whole table and sort the keys, so it costs I/O and CPU in proportion to the table’s size: 1.4 seconds for one int column on our 1,000,000 rows, much longer on real tables. It also needs temporary disk space for the sort and for the log of concurrent changes.

That log is capped by innodb_online_alter_log_max_size (128 MB by default). If concurrent writes during a long build overflow it, the ALTER fails with DB_ONLINE_LOG_TOO_BIG and its work is lost; the MySQL manual says uncommitted concurrent DML is rolled back too.

Other cases we checked on MySQL 8.4:

• ALGORITHM=INSTANT isn’t available for adding an index: ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
• FULLTEXT: ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED. Plan for writes to stop.
• SPATIAL: ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Do not support online operation on table with GIS index. Try LOCK=SHARED.
• UNIQUE: builds online, but fails at the end if the data has duplicates: ERROR 1062 (23000): Duplicate entry '2' for key 'orders.uq_customer'. Check first with GROUP BY … HAVING COUNT(*) > 1.
• ALGORITHM=COPY would rebuild the table and block writes: ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

Dropping or renaming an index is a quick in-place change that only touches metadata, though neither accepts ALGORITHM=INSTANT on MySQL 8.4 (error 1845).

Run it safely

SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;

1. Set lock_wait_timeout to a few seconds, so a wait for the metadata lock fails with error 1205 instead of queueing the table behind it. On MySQL it defaults to a year.
2. Check for long transactions in information_schema.INNODB_TRX before you start, and avoid starting long reports on the table while the index builds, or the build will wait at the end.
3. Run it when writes are lighter if the table is large, so the change log stays small and the final step, which applies it, is short.
4. Check disk space: the sort needs room in the temporary directory.

MariaDB

MariaDB 11.4 behaved the same in our tests: ALGORITHM=INPLACE, LOCK=NONE built the index while an INSERT and an UPDATE went through immediately (0.522 seconds for the build), the FULLTEXT case failed with the same LOCK=NONE is not supported message, and the end-of-build wait on an open transaction was the same (5.276 seconds; the queued SELECT took 2.434 seconds).

The differences:

• MariaDB also has ALGORITHM=NOCOPY, which accepts index builds; its error for INSTANT says so: ALGORITHM=INSTANT is not supported. Reason: ADD INDEX. Try ALGORITHM=NOCOPY.
• ALGORITHM=COPY, LOCK=NONE is accepted on MariaDB 11.4: it copied the table (1,000,002 rows affected, 2.27 seconds) while allowing writes. It’s slower than building in place, so there’s no reason to choose it for an index.
• ALTER TABLE orders WAIT 5 ADD INDEX … limits the metadata lock wait for one statement.

How we checked

A 1,000,000-row orders table (see ADD COLUMN for the definition) on MySQL 8.4.11 and MariaDB 11.4.13:

ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
CREATE INDEX idx_status ON orders (status) ALGORITHM=INPLACE LOCK=NONE;
ALTER TABLE orders ADD FULLTEXT INDEX ft_note (note), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders ADD UNIQUE INDEX uq_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE orders ADD INDEX idx_created (created_at), ALGORITHM=COPY, LOCK=NONE;

MySQL 8.4.11:

ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INSTANT
ERROR 1845 (0A000) at line 1: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE
Query OK, 0 rows affected (1.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE INDEX idx_status ON orders (status) ALGORITHM=INPLACE LOCK=NONE
Query OK, 0 rows affected (1.74 sec)
Records: 0  Duplicates: 0  Warnings: 0

ALTER TABLE orders ADD FULLTEXT INDEX ft_note (note), ALGORITHM=INPLACE, LOCK=NONE
ERROR 1846 (0A000) at line 4: LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED.

ALTER TABLE orders ADD UNIQUE INDEX uq_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE
ERROR 1062 (23000) at line 5: Duplicate entry '2' for key 'orders.uq_customer'

ALTER TABLE orders ADD INDEX idx_created (created_at), ALGORITHM=COPY, LOCK=NONE
ERROR 1846 (0A000) at line 8: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED.

For concurrency, we started the ALTER in one session, then sent an INSERT and an UPDATE from another 0.4 seconds later and timed them; for the metadata lock cases, a third session held a transaction open on the table.

In Inlet

Inlet’s structure editor shows the exact ALTER TABLE or CREATE INDEX before it runs and warns when a change rewrites or scans the table. The Activity monitor for MySQL and MariaDB lists sessions, so you can see an ALTER waiting and the connection it’s waiting for.