How to add an index in MySQL without downtime

Quick answer: On MySQL 8.0 with InnoDB, a normal secondary index builds in place while reads and writes continue. State ALGORITHM=INPLACE, LOCK=NONE explicitly: those clauses are assertions, so MySQL errors out instead of silently falling back to a blocking build. Three common changes refuse them and need gh-ost or pt-online-schema-change: a FULLTEXT index, a SPATIAL index, and a column type change. An online ALTER that hangs is usually waiting on an old transaction, not doing work.

On MySQL 8.0 with InnoDB, adding a normal secondary index does not require downtime. The build runs in place while concurrent reads and writes continue.

The distinction matters because “online” is a property of the specific change you are making, not of ALTER TABLE. When MySQL cannot perform your change online, it normally falls back to a blocking build rather than refusing the statement.

Ask explicitly, so failure is loud

CREATE DATABASE ddl_demo;
USE ddl_demo;

CREATE TABLE events (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id    INT NOT NULL,
  kind       VARCHAR(40) NOT NULL,
  created_at DATETIME NOT NULL
);

INSERT INTO events (user_id, kind, created_at)
SELECT FLOOR(1 + RAND() * 1000),
       ELT(FLOOR(1 + RAND() * 4), 'login', 'view', 'purchase', 'logout'),
       NOW() - INTERVAL FLOOR(RAND() * 200) DAY
FROM information_schema.columns a, information_schema.columns b
LIMIT 20000;

Now add the index this way in production:

ALTER TABLE events
  ADD INDEX idx_user_created (user_id, created_at),
  ALGORITHM=INPLACE,
  LOCK=NONE;

Those two clauses are assertions, not requests:

• ALGORITHM=INPLACE: build in place rather than copying the table. If this change cannot be done in place, the statement fails with an error instead of quietly copying.
• LOCK=NONE: reads and writes must continue throughout. If MySQL cannot guarantee that, it errors rather than locking your table.

Without the clauses, an operation that cannot be done online can run with writes blocked, often as a full table copy. You may find out from the error rate instead of from MySQL.

What still takes the table

Each row below was checked against MySQL 8.4 by running the ALTER with ALGORITHM=INPLACE, LOCK=NONE and recording whether the server accepted it.

Change	INPLACE, LOCK=NONE accepted?
Add or drop a secondary index	Yes
Rename an index	Yes
Add a column	Yes, and ALGORITHM=INSTANT works for most cases
Add a primary key where none exists	Yes, but it rebuilds the whole table
Change a column’s type	No. ERROR 1846: Cannot change column type INPLACE
Add a FULLTEXT index	No. ERROR 1846: Fulltext index creation requires a lock
Add a SPATIAL index	No. ERROR 1846: Do not support online operation

Adding a primary key is the row that surprises people, in both directions. MySQL accepts it online and writes continue, so it is not the table-locking operation it is often described as. But it rebuilds the clustered index, which means rewriting every row and every secondary index. On a large table, that can mean hours of I/O and a temporary second copy of the table on disk. Online is not the same as cheap.

For the three refusals, use gh-ost or pt-online-schema-change. They build a shadow table and swap it in. That is a different technique with its own risks, not a flag you can add.

The failure that looks like online DDL hanging

Even a fully online ALTER needs a brief exclusive metadata lock at the end, and sometimes at the start.

If a long-running transaction is holding that table, the ALTER waits for the lock, and every query that arrives after it queues behind the ALTER. A table that was fine a second ago stops responding entirely, and the ALTER gets the blame.

The ALTER is not the cause. Find the transaction that is actually holding the table:

SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'ddl_demo' AND LOCK_STATUS = 'PENDING';

Two habits prevent this:

SET SESSION lock_wait_timeout = 10;

Bound the wait. With a short lock_wait_timeout, the ALTER gives up after ten seconds instead of holding the door open indefinitely while a queue forms behind it. Retry it later; that costs you nothing.

Check for old transactions before you start, not after:

SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

Replication is the other cost

On a self-managed replication topology, the index build runs on the replica too. With single-threaded apply, it runs after everything queued ahead of it. A build that took four minutes on the primary can show up as four minutes of replica lag, which matters if you route reads there.

Check before and after:

SHOW REPLICA STATUS;

Clean up

DROP DATABASE ddl_demo;

How DBGorilla helps

DBGorilla can help

DBGorilla snapshots your schema through mysqldump, so the index you added is recorded and your AI coding agent can tell you what is actually on the table rather than what the migration meant to put there. While a long ALTER is running, the digest statistics and wait events show whether the build is making progress or whether everything behind it is queued on the metadata lock this article warns about. It surfaces and explains; it does not write to your database.

Start free

FAQ

• Does CREATE INDEX block writes in MySQL?

  For a normal secondary index on InnoDB, no. MySQL 8.0 defaults to an in-place build that permits concurrent reads and writes. But it is worth stating ALGORITHM=INPLACE, LOCK=NONE explicitly so that MySQL errors out rather than silently falling back to a blocking build for an operation that cannot be done online.

• What is a metadata lock and why did my online DDL still hang?

  Online DDL still needs a brief exclusive metadata lock at the end of the operation, and sometimes at the start. A long-running transaction holding the table blocks that lock, and every query arriving afterwards queues behind it. The DDL looks stuck; the real culprit is the old transaction.

• Which index changes cannot be done online?

  On MySQL 8.4, three common ones refuse ALGORITHM=INPLACE, LOCK=NONE outright and fail with ERROR 1846: adding a FULLTEXT index, adding a SPATIAL index, and changing a column's type. Dropping a primary key without adding a new one cannot be done online either. Use gh-ost or pt-online-schema-change for those. Adding a primary key is accepted online, but it rebuilds the whole table, so it is online without being cheap.

• Is ALTER TABLE ... ADD INDEX different from CREATE INDEX?

  No, they compile to the same operation on InnoDB. Both forms accept ALGORITHM and LOCK clauses.

Related

• Why is MySQL not using my index?
• How to read a MySQL EXPLAIN plan
• How to fix “Too many connections” in MySQL
• Product: DBGorilla docs