Description:
## Feature: EXPLAIN ALTER TABLE
### Problem Statement
MySQL's `ALTER TABLE` supports three execution algorithms — **INSTANT**,
**INPLACE**, and **COPY** — with fundamentally different performance and
availability characteristics:
| Algorithm | Mechanism | Typical Duration | Write Concurrency |
|-----------|-----------|-----------------|-------------------|
| INSTANT | Metadata-only, no rebuild | Milliseconds | Unblocked |
| INPLACE | Online DDL, no full copy | Seconds to minutes | Unblocked (or reads-only) |
| COPY | Full table rebuild via temp copy | Minutes to hours | **Blocked** |
The algorithm actually chosen depends on a complex combination of factors:
storage engine capabilities, operation type, table schema, row format,
foreign key presence, session variables (`old_alter_table`), and any
explicit `ALGORITHM=` / `LOCK=` hints. **None of this is observable
without actually executing the statement.**
This creates a real operational problem:
1. **No preview without execution.** A DBA who wants to know whether
`ALTER TABLE orders MODIFY COLUMN amount DECIMAL(20,4)` will block
writes for 30 minutes or complete instantly must either run it on
production (risky), test it on a replica (time-consuming), or guess
from documentation (unreliable — behavior varies by schema, engine
version, and configuration).
2. **Documentation cannot cover all cases.** The manual describes which
operations *generally* support online DDL, but actual algorithm
selection also depends on runtime state: whether `old_alter_table` is
set, whether the table is temporary, whether `foreign_key_checks` is
ON, whether combined clauses interact, and what the engine returns
for the specific operation set.
3. **`ALGORITHM=` is not a dry-run tool.** Specifying `ALGORITHM=INPLACE`
either forces the algorithm (causing an error if unsupported) or
confirms the user's choice — it does not reveal what would have been
chosen automatically.
4. **No equivalent to `EXPLAIN` for DDL.** MySQL users have long had
`EXPLAIN SELECT` to understand query execution before running it. DDL
has no such capability, despite DDL often carrying far more severe
performance consequences than a slow query.
---
### Proposed Solution
Add `EXPLAIN ALTER TABLE` (also accepted as `DESC ALTER TABLE` /
`DESCRIBE ALTER TABLE`), which returns a single-row result set describing
the algorithm, lock level, and reason — without executing the DDL:
```sql
mysql> EXPLAIN ALTER TABLE orders ADD INDEX idx_status(status);
+--------+-----------+------+------------------------------------+
| Table | Algorithm | Lock | Note |
+--------+-----------+------+------------------------------------+
| orders | INPLACE | NONE | Online DDL, concurrent read/write |
+--------+-----------+------+------------------------------------+
mysql> EXPLAIN ALTER TABLE orders MODIFY COLUMN amount DECIMAL(20,4);
+--------+-----------+--------+----------------------------------------------+
| Table | Algorithm | Lock | Note |
+--------+-----------+--------+----------------------------------------------+
| orders | COPY | SHARED | Table rebuild required, concurrent read only |
+--------+-----------+--------+----------------------------------------------+
```
How to repeat:
None
