Storage, Performance and Concurrency Considerations


In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary table is created in the database directory of the new table. This can differ from the database directory of the original table for ALTER TABLE operations that rename the table to a different database.

For MyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting the myisam_sort_buffer_size [516] system variable to a high value.

For some operations, an in-place ALTER TABLE is possible that does not require a temporary table:

• For ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See RENAME TABLE Syntax.) Any privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.
• Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. The following changes are fast alterations that can be made this way:
• Renaming a column, except for the InnoDB storage engine before MySQL 5.6.6.
• Changing the default value of a column.
• Changing the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values, as long as the storage side of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this will require a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.
• ALTER TABLE ... ADD PARTITION creates no temporary table. ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between changed partitions; unless LINEAR HASH or LINEAR KEY was used, this is much the same as creating a new table (although the operation is done partition by partition). REORGANIZE operations copy only changed partitions and do not touch unchanged ones.
• Renaming an index, except for InnoDB.
• Adding or dropping an index, for InnoDB.
You can force an ALTER TABLE operation that would otherwise not require a table copy to use the temporary table method (as supported in MySQL 5.0) by setting the old_alter_table [520] system variable to ON, or specifying ALGORITHM=COPY as one of the alter_specification clauses. If there is a conflict between the old_alter_table setting and an ALGORITHM clause with a value other than DEFAULT, the ALGORITHM clause takes precedence. (ALGORITHM = DEFAULT is the same a specifying no ALGORITHM clause at all.)

Specifying ALGORITHM=INPLACE makes the operation use the in-place technique for clauses and storage engines that support it, and fail with an error otherwise, thus avoiding a lengthy table copy if you try altering a table that uses a different storage engine than you expect. See Online DDL for InnoDB Tables for information about online DDL for InnoDB tables.

You can control the level of concurrent reading and writing of the table while it is being altered, using the LOCK clause. Specifying a non-default value for this clause lets you require a certain amount of concurrent access or exclusivity during the alter operation, and halts the operation if the requested degree of locking is not available. The parameters for the LOCK clause are:

• LOCK = DEFAULT
Maximum level of concurrency for the given ALGORITHM clause (if any) and ALTER TABLE operation: Permit concurrent reads and writes if supported. If not, permit concurrent reads if supported. If not, enforce exclusive access.

• LOCK = NONE
If supported, permit concurrent reads and writes. Otherwise, return an error message.

• LOCK = SHARED
If supported, permit concurrent reads but block writes. Note that writes will be blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. If concurrent reads are not supported, return an error message.

• LOCK = EXCLUSIVE
Enforce exclusive access. This will be done even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.

As of MySQL 5.6.3, you can also use ALTER TABLE tbl_name FORCE to perform a “null” alter operation that rebuilds the table. Previously the FORCE option was recognized but ignored.