MySQL Alter Table

In MySQL databases, especially when dealing with large tables, structural modifications can sometimes cause issues that give DBAs a hard time. In this article, we’ll explore possible problems, their causes, and solutions that may arise during ALTER TABLE operations.

In MySQL, the ALTER TABLE statement allows you to modify the structure of an existing table.

The ALGORITHM clause specifies how MySQL performs the alteration. There are three possible values for the ALGORITHM option:

ALGORITHM = INPLACE

Instead of creating a copy of the table, this algorithm performs the alteration directly on the existing data file.

Use cases:
Adding or dropping indexes, renaming columns, changing column data types, adding or dropping partitions in partitioned tables, and performing partition COALESCE or REORGANIZE operations.

Performance:
Since it does not create a copy of the table, it is faster and requires no additional disk space — a major advantage for large tables. Also, because a second copy of the table is not written to disk, DML operations (INSERT, UPDATE, DELETE) can continue during the process.

However, be aware that crashes during INPLACE operations can lead to file corruption, so this risk should not be overlooked.

Example:

ALTER TABLE my_table ADD INDEX idx_name (name)

ALTER TABLE my_table ADD INDEX idx_name (name)

ALTER TABLE my_table ADD INDEX idx_name (name)

ALGORITHM = COPY

This algorithm creates a temporary copy of the table and applies changes to that copy. During this time, DML operations cannot be performed on the table.
Once the modification is complete, MySQL replaces the original table with the modified copy and deletes the old one.

This method is preferred for longer and more complex operations, such as modifying the primary key, adding a STORED column, or changing the order of columns, where the table needs to be rewritten completely.

Because a full copy of the table is created, the process takes longer and consumes additional disk space equal to the size of the table.
However, since the original table remains untouched until the process finishes, it is a safer option in case of a crash.

Example:

ALTER TABLE my_table ADD COLUMN

ALTER TABLE my_table ADD COLUMN

ALTER TABLE my_table ADD COLUMN

For large tables, ALTER operations using the COPY algorithm can be slow.
In systems where downtime must be minimized, using pt-online-schema-change is highly recommended.

ALGORITHM = DEFAULT

This allows MySQL to automatically choose the most suitable algorithm between INPLACE and COPY.
If you’re not sure which one to use, leaving the choice to MySQL is generally the safer option.