Online DDL in MySQL: Understanding ALGORITHM=INSTANT vs. INPLACE vs. COPY

1. Why the ALGORITHM parameter of ALTER TABLE matters

Before MySQL 5.6, the server had essentially one way to handle any schema change: rebuild the affected table from scratch. A new column, a new index, a reordered column, all of it triggered a full table copy with an exclusive lock for the entire duration. On a catalog_product_entity with several million rows, that could mean hours of blocked writes.

With the online DDL framework, available since MySQL 5.6 and continuously extended through 8.0, the server distinguishes between three algorithms for every supported ALTER TABLE operation: INSTANT, INPLACE and COPY. Which algorithm actually applies is not up to the developer, it is strictly determined by the concrete operation. Anyone who does not know the difference blindly relies on whatever default MySQL picks automatically and gets surprised by long runtimes.

2. ALGORITHM=COPY: the most expensive, slowest variant

COPY is historically the only algorithm that ever existed and remains the fallback today for every operation that supports neither instant nor inplace. The server creates a brand new, empty table with the target structure, copies rows one by one from the old table into the new one, rebuilds every index, and finally swaps both tables atomically. Read access is usually still tolerated during the copy phase, but writes are blocked.

Disk space temporarily doubles, since the old and new table coexist before the old version gets dropped. On an 80 gigabyte sales_order_grid, that means 160 gigabytes of free disk space just to rename a single column, if MySQL falls back to COPY for whatever reason. Operations such as changing the character encoding of an entire table, or certain foreign key changes, still force the server into this mode even in MySQL 8.0.

3. ALGORITHM=INPLACE: no rebuild, but not free either

INPLACE avoids the full table copy at the SQL level, but internally it still often performs a rebuild of the data file, just within the existing table structure and without a temporary second table in the data directory. During the operation, InnoDB allows concurrent DML statements and logs them into a so called row log, which is applied to the new structure once the actual restructuring work finishes. Only a brief exclusive metadata lock remains at the end for the final swap.

That sounds like an almost free operation, but it is not always. Adding a secondary index, for example, runs inplace, yet still generates full I/O effort because the entire index has to be sorted and built from scratch. On a very write-heavy table, the row log can also hit its configured ceiling innodb_online_alter_log_max_size, which ends the whole, potentially hours-long operation with a rollback, without applying the change at all.

4. ALGORITHM=INSTANT: when only metadata really changes

INSTANT is the newest and fastest of the three algorithms. Instead of touching data at all, the server only changes an entry in the data dictionary and internally remembers which physical rows still exist in the old format. Only on the next regular access to such a row does InnoDB transparently interpret it using a so called instant metadata version, without needing to rewrite the file itself. The runtime of an instant-capable operation is therefore independent of table size and practically always lands in the millisecond range.

Since MySQL 8.0.12 this works for adding a column at the end of a table, since 8.0.29 also for adding and dropping columns at any position as well as making columns visible or invisible. On a url_rewrite table with several hundred million rows, the difference between an INSTANT operation finishing in under a second and a COPY operation grinding the shop to a halt for hours is business-critical.

-- Explicitly require an operation to run INSTANT only
-- Fails predictably instead of silently falling back to COPY
ALTER TABLE catalog_product_entity
  ADD COLUMN internal_note VARCHAR(255) NULL,
  ALGORITHM=INSTANT;

-- Check whether the server actually used INSTANT
SELECT NAME, TABLE_ID
FROM information_schema.INNODB_TABLES
WHERE NAME = 'mironsoft/catalog_product_entity';

5. Which operations actually use which algorithm

The exact mapping is fully documented by MySQL, but in practice it is worth looking at the most common cases in Magento's day to day. Appending columns at the end, changing column defaults, renaming columns and renaming indexes practically always run instant. Adding or dropping a secondary index runs inplace, as does changing certain row format options. Converting a column to an incompatible data type, for example from INT to VARCHAR, or switching an entire table to a different character encoding scheme, still forces COPY.

Also important: changing a table's primary key always runs as a rebuild, because it changes the physical sort order of every row in the clustered InnoDB structure. Even when MySQL technically classifies it as INPLACE, the I/O effort is comparable to a full copy. Anyone unsure should not guess the concrete operation, but test it against a copy of the production table with a realistic row count before running it live in a maintenance window.

6. Forcing the algorithm explicitly and measuring runtime

Instead of relying on MySQL's automatic choice, every production ALTER TABLE statement should explicitly request the expected algorithm via the ALGORITHM= clause. If the actual operation does not match the requested algorithm, the server aborts immediately with an error instead of silently falling back to the next slower variant. That prevents the common surprise where a seemingly harmless ALTER TABLE suddenly triggers a full table copy during a maintenance window.

It is also worth adding the LOCK=NONE clause, which demands that concurrent reads and writes keep working throughout the entire operation. Again, if the concrete operation cannot support that, the statement aborts in a controlled way instead of silently locking the shop out of orders during a deploy.

-- Make sure writes keep flowing during the alter
ALTER TABLE sales_order_grid
  ADD INDEX idx_customer_email (customer_email),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Watch runtime and progress of a running online DDL operation
SELECT * FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter table%';

7. Row format requirements and the limits of INSTANT

INSTANT operations require the table to use the DYNAMIC or COMPRESSED row format. Tables in the legacy COMPACT or REDUNDANT format, occasionally left over from very old Magento 1 migrations, do not support instantly added columns and automatically fall back to COPY. A one-time ALTER TABLE ... ROW_FORMAT=DYNAMIC, even as an expensive one-off operation, pays off in the long run.

InnoDB also caps the number of instantly added columns per table at 64 additional versions before an internal rebuild gets forced. In practice, hardly any Magento project ever hits that, but the limit still matters on very actively maintained, EAV-adjacent custom tables with many small schema changes accumulated over years. One more point: database dumps and downgrades to older server versions can run into trouble with instant-created row formats, a clean mysqldump that fully rebuilds the target table reliably sidesteps that.

8. Impact on large Magento tables in practice

Tables like catalog_product_entity, catalog_product_index_price, sales_order or url_rewrite quickly grow into double-digit gigabyte sizes in established shops. Magento's declarative schema in db_schema.xml leaves the concrete algorithm choice to the server, so a module update during setup:upgrade can silently trigger a COPY operation on a huge table and stretch the deploy process by hours without any visibility beforehand.

Before any production module update that brings schema changes to large tables, it is worth running a test against a current copy of the production database with a realistic row count, including measuring the actual runtime. Once it is clear an operation requires COPY, for example a type change on a heavily used column, external tools like pt-online-schema-change or gh-ost are often the better choice over the native, blocking ALTER TABLE.

9. Detecting and fixing failed online DDL operations

Even a fundamentally instant- or inplace-capable operation can get stuck, usually because it needs a metadata lock at the start that a long running transaction is holding. SHOW PROCESSLIST shows the ALTER command in the state Waiting for table metadata lock in that case. Deeper insight comes from the performance_schema.metadata_locks table, which lists every currently held and requested lock along with its session ID.

If that reveals an hours-old, unfinished transaction from a stale admin tab or a stuck cron process, the usual fix is to kill that session with KILL before the ALTER operation can proceed. For an already running INPLACE operation that fails at the row log limit, the only fix is a clean rollback and a second attempt with a temporarily raised innodb_online_alter_log_max_size, or during a maintenance window with less concurrent write load.

10. Summary

11. FAQ: Online DDL: INSTANT vs. INPLACE vs. COPY