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 table copy. Three 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 and concurrent reads and writes continue.

The reason this still goes wrong is that “online” is not a property of ALTER TABLE, it is a property of the specific change you are making. When MySQL cannot do your change online, the default behaviour is to fall back to a blocking copy rather than to refuse.

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 the way you should add it 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.

That is the entire point of writing them. Without the clauses, an operation that cannot be done online silently becomes a full table copy with writes blocked, and you find out from your error rate rather than 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 is 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, which 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 start and the end.

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 the victim, not the cause. Find the transaction that is actually holding it:

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 it:

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.

And 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, and on a 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;

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