# 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

```sql
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:

```sql
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:

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

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

Two habits prevent it:

```sql
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:

```sql
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:

```sql
SHOW REPLICA STATUS;
```

## Clean up

```sql
DROP DATABASE ddl_demo;
```

## Related

- [Why is MySQL not using my index?](https://www.dbgorilla.com/learn/mysql/why-is-mysql-not-using-my-index/)
- [How to read a MySQL EXPLAIN plan](https://www.dbgorilla.com/learn/mysql/how-to-read-a-mysql-explain-plan/)
- [How to fix "Too many connections" in MySQL](https://www.dbgorilla.com/learn/mysql/how-to-fix-too-many-connections-in-mysql/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)