MySQL 8.4 Schema Safety

MySQL ALGORITHM=INSTANT Checker

Paste your ALTER TABLE statement below to check whether MySQL 8.4 InnoDB can execute it via metadata-only ALGORITHM=INSTANT or if it will trigger an INPLACE / COPY table rebuild.

100% browser-based • Zero SQL sent over the network

How MySQL 8.4 ALGORITHM=INSTANT Works

On production InnoDB tables with tens of millions of rows, an unguarded ALTER TABLE can saturate disk I/O, stall replication replicas, and trigger metadata lock pileups across PHP-FPM workers. When an operation supports ALGORITHM=INSTANT, MySQL 8.4 updates only the data dictionary metadata without touching existing data pages—completing in milliseconds regardless of table size.

DDL Operations Supported by INSTANT in MySQL 8.4

  • Adding columns at any position: Unlike MySQL 8.0.12–8.0.28 (which restricted instant additions to the trailing column), MySQL 8.0.29+ and 8.4 use InnoDB row versioning to support ADD COLUMN ... AFTER col_name instantly.
  • Dropping columns: DROP COLUMN completes instantly unless the column is referenced by a primary key, secondary index, or foreign key constraint.
  • Renaming & default changes: Renaming a column without changing its type or modifying SET DEFAULT / DROP DEFAULT executes as metadata-only.

Edge Cases That Force an INPLACE or COPY Rebuild

  • The 64 Row-Version Ceiling: Each instant column add or drop increments TOTAL_ROW_VERSIONS in INFORMATION_SCHEMA.INNODB_TABLES. Once a table hits 64 row versions, the next DDL forces a full table rebuild (OPTIMIZE TABLE resets this counter).
  • FULLTEXT Indexes & Compressed Tables: Tables containing a FULLTEXT index or configured with ROW_FORMAT=COMPRESSED cannot use ALGORITHM=INSTANT.
  • Column Type or Nullability Changes: Switching NULL to NOT NULL or changing data types requires scanning existing rows.

Frequently Asked Questions

How do I enforce INSTANT inside a Laravel migration?

Instead of relying on Laravel's blueprint builder (which omits explicit algorithm clauses), execute a raw DB::statement() appending ALGORITHM=INSTANT, LOCK=NONE. If MySQL cannot satisfy it instantaneously, it fails immediately rather than silently locking production.

Can ALGORITHM=INSTANT still block queries?

Yes, briefly. MySQL still acquires an exclusive Metadata Lock (MDL) for a split second at the start and commit phase. If a long-running analytical query or unclosed transaction is open on the table, the DDL waits behind it and queues subsequent writes.