Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL ALTER TABLE takes hours on a big table

ErrorALTER TABLE runs for hours on a large table

Short answer

An ALTER TABLE is slow when it has to rebuild the table. Changing a column's data type always copies the whole table. Adding a column, renaming one or setting a default is a metadata change and is near instant in current MySQL. Name the algorithm explicitly: MySQL then either uses it or fails with an error, and never silently falls back to a copy.

  • Applies to MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Decide which operation you really run, then ask MySQL for the cheapest algorithm. If it cannot do it that way, MySQL returns an error instead of copying the table.

    ALTER TABLE orders ADD COLUMN note VARCHAR(100), ALGORITHM=INSTANT;
  2. 2Match what you see, then jump to its fix

    1. 1The statement changes INT to BIGINT, VARCHAR to TEXT, a character set, or similarChanging a column's data type needs a full table copyFix
    2. 2You extend VARCHAR(100) to VARCHAR(300) or similar and it is slowA VARCHAR is being grown across the 255 to 256 byte boundaryFix
    3. 3Reordering columns, or changing NULL to NOT NULLThe change rebuilds the table in placeFix

What does the error look like?

  • ALTER TABLE runs for hours on a large table
  • Writes to the table wait while the ALTER runs

Cause 1 of 3

Changing a column's data type needs a full table copy

1Check

Changing the data type is supported only with ALGORITHM=COPY, which rebuilds the table and does not allow concurrent DML.

ALTER TABLE orders MODIFY id BIGINT, ALGORITHM=INPLACE;

2Fix

Plan it as a maintenance job, off-peak. If the downtime is too long, build the new column or table beside the old one and move rows in batches.

ALTER TABLE orders ADD COLUMN id_big BIGINT, ALGORITHM=INSTANT;
-- fill id_big in small batches, then switch the application

3Verify

The first statement finishes in moments, because adding a column is a metadata change.

SHOW CREATE TABLE orders\G

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

A VARCHAR is being grown across the 255 to 256 byte boundary

1Check

Growing a VARCHAR is an in-place change only while it stays under 256 bytes or stays at 256 bytes or more. Crossing that boundary is not supported in place, and shrinking a VARCHAR needs a table copy.

SELECT column_name, character_maximum_length FROM information_schema.columns WHERE table_name = 'orders';

2Fix

If you can, choose a size that stays on the same side of the boundary. Remember that the limit is in bytes, so a multibyte character set counts several bytes per character.

ALTER TABLE orders MODIFY name VARCHAR(200), ALGORITHM=INPLACE, LOCK=NONE;

3Verify

The statement is accepted with the clause, or fails with an error at once instead of copying.

SHOW WARNINGS;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The change rebuilds the table in place

1Check

These run in place but may rebuild the table, so they take time proportional to the table size even though DML is allowed.

ALTER TABLE orders MODIFY note VARCHAR(100) NOT NULL, ALGORITHM=INSTANT;

2Fix

Run these when load is low, and state ALGORITHM=INPLACE, LOCK=NONE so a statement that would block writes is refused.

ALTER TABLE orders MODIFY note VARCHAR(100) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

3Verify

The statement runs while writes continue.

SHOW PROCESSLIST;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Always write ALGORITHM and LOCK in production ALTER statements.
  • Test the exact statement on a copy of the table to see how long it takes.
  • Choose column types with room to grow, so later changes stay metadata-only.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.