Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A short ALTER TABLE statement can trigger a full table rebuild or substantial data reorganization. The SQL’s apparent simplicity does not reveal the work: behavior depends on the database product and version, storage engine, exact clause, and algorithm selected. In MySQL 8.4, for example, INPLACE does not guarantee that no rebuild occurs.
Does ALTER TABLE rebuild a table?
Sometimes. An ALTER TABLE can change only metadata, reorganize existing data, or copy rows into a replacement table. Which outcome applies is specific to the database and the exact operation; it cannot be inferred from the statement’s length or from the fact that the change appears small.
For MySQL 8.4, the official manual distinguishes three algorithms: COPY, INPLACE, and INSTANT. These names describe different kinds of work, not a universal guarantee about every database’s DDL behavior.
What COPY, INPLACE, and INSTANT mean in MySQL 8.4
| Algorithm | Effect on table data | Concurrency and locking |
|---|---|---|
INSTANT |
Changes metadata only; table data is unaffected. | MySQL documents brief exclusive metadata locks for some phases. Exact behavior depends on the operation. |
INPLACE |
Avoids copying table data, but may rebuild the table in place. | Typically permits concurrent DML, subject to operation-specific constraints. Some phases may take a brief exclusive metadata lock. |
COPY |
Works on a copy of the original table and copies rows to it. | Concurrent DML is not permitted during the operation. |
MySQL’s definition is explicit: “INPLACE: Operations avoid copying table data but may rebuild the table in place.” That is why “in place” must not be read as “no rebuild,” “no work,” or “no operational impact.”
#1 Best Overall
When you omit an algorithm in MySQL 8.4, the server uses INSTANT for operations that support it, otherwise INPLACE, then COPY if INPLACE is unsupported. If you explicitly request an algorithm the operation does not support, the statement fails with an error. The supported choice is operation- and engine-specific, not freely interchangeable.
Which MySQL InnoDB changes can rebuild a table?
Adding a primary key
MySQL’s InnoDB online DDL operation table identifies adding a primary key as a table rebuild. It also explains that the operation substantially reorganizes the data. This is a concrete example of a modest-looking schema change involving significant work; it does not mean every ALTER TABLE rebuilds a table.
Rank #2
Rebuilding with an engine alteration
MySQL documents ALTER TABLE t1 ENGINE = InnoDB; as a “null” alteration that can rebuild a table already using InnoDB. “Null” here does not mean the command is guaranteed to do nothing: it is a documented way to rebuild. See MySQL’s ALTER TABLE reference.
Free tools Windows power users keep installed
One-click scans. No signup required.
How to check an ALTER TABLE before running it
- Identify the actual environment. Record the database product and release, the table’s storage engine, and the complete
ALTER TABLEstatement. A result documented for another version, engine, or product may not apply. - Find the exact operation in the matching manual. For MySQL, consult the online DDL operation-support table for the clause and engine. Confirm whether the operation is instant, in place, or copy-based, and whether it rebuilds or reorganizes data.
- Check the operational effects. Verify whether concurrent reads and writes are permitted and which locks are taken, including any exclusive metadata lock during preparation or execution.
- Use an explicit algorithm only when supported. In MySQL, requesting an unsupported algorithm causes an error rather than making the operation use that method. Confirm support for the exact operation before relying on the clause.
Do not infer runtime, disk-space needs, outage length, or lock duration from the SQL alone. Those consequences cannot be determined without the exact statement, database release, engine, schema, table size, and workload.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.PostgreSQL has rewrite behavior, but different rules
The same general caution applies outside MySQL, but MySQL’s algorithm names and concurrency rules should not be carried over. The PostgreSQL 18 ALTER TABLE reference states that some forms of ALTER TABLE force a table rewrite. It also names VACUUM FULL and CLUSTER as operations that can rewrite a table. Check the documentation for the deployed PostgreSQL version and exact form of the command.
Quick Recap
Best Value
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
