Re: adding index to a table

[Rick James]

Sure. That is a complex way to add an index, and it nearly avoids any downtime.

Prerequisite: Some way to know what has changed. For example: you have an AUTO_INCREMENT id and only INSERT, not UPDATE.

1. CREATE the new version of the table without any indexes (If InnoDB, do include the PRIMARY KEY.)
2. LOCK the table to prevent writes
3. Copy the table (downtime against writes, but not reads) -- INSERT SELECT
4. UNLOCK
5. ALTER the new table (ADD INDEX, etc)
6. LOCK original table. Caveat: not sure how to do the UNLOCK.
7. copy any _new_ records to new table (brief downtime)
8. RENAME TABLE tbl TO old, new TO tbl; (instantaneous, except for gaining access to the table)

A variant, which avoids (mostly) the LOCKs:
1. CREATE the new table (with new indexes, etc)
2. Loop, Copying a thousand rows at a time.
3. When finished, LOCK the old table, copy the last few rows
4. RENAME TABLE tbl TO old, new TO tbl; (instantaneous, except for gaining access to the table)

Again, this requires a safe way to walk through the records without missing any updates.