14.11.1 Overview of Online DDL

Historically, many DDL operations on InnoDB tables were expensive. Many ALTER TABLE operations worked by creating a new, empty table defined with the requested table options and indexes, then copying the existing rows to the new table one-by-one, updating the indexes as the rows were inserted. After all rows from the original table were copied, the old table was dropped and the copy was renamed with the name of the original table.

MySQL 5.5, and MySQL 5.1 with the InnoDB Plugin, optimized CREATE INDEX and DROP INDEX to avoid the table-copying behavior. That feature was known as Fast Index Creation. MySQL 5.6 enhances many other types of ALTER TABLE operations to avoid copying the table. Another enhancement allows SELECT queries and INSERT, UPDATE, and DELETE (DML) statements to proceed while the table is being altered. This combination of features is now known as online DDL.

This new mechanism also means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without any secondary indexes, then adding the secondary indexes after the data is loaded.

Although no syntax changes are required in the CREATE INDEX or DROP INDEX commands, some factors affect the performance, space usage, and semantics of this operation (see Section 14.11.9, “Limitations of Online DDL”).

The online DDL enhancements in MySQL 5.6 improve many DDL operations that formerly required a table copy, blocked DML operations on the table, or both. Table 14.5, “Summary of Online Status for DDL Operations” shows the variations of the ALTER TABLE statement and shows how the online DDL feature applies to each one.

With the exception of ALTER TABLE partitioning clauses, online DDL operations for partitioned InnoDB tables follow the same rules that apply to regular InnoDB tables. For more information, see Section 14.11.8, “Online DDL for Partitioned InnoDB Tables”.

Table 14.5 Summary of Online Status for DDL Operations

OperationIn-Place?Copies Table?Allows Concurrent DML?Allows Concurrent Query?Notes
CREATE INDEX, ADD INDEXYes*No*YesYesSome restrictions for FULLTEXT index; see next row. Currently, the operation is not in-place (that is, it copies the table) if the same index being created was also dropped by an earlier clause in the same ALTER TABLE statement.
ADD FULLTEXT INDEXYesNo*NoYesCreating the first FULLTEXT index for a table involves a table copy, unless there is a user-supplied FTS_DOC_ID column. Subsequent FULLTEXT indexes on the same table can be created in-place.
DROP INDEXYesNoYesYesModifies .frm file only, not the data file.
OPTIMIZE TABLEYesYesYesYesUses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=COPY is used if old_alter_table=1 or mysqld --skip-new option is enabled. OPTIMIZE TABLE using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes.
Set default value for a columnYesNoYesYesModifies .frm file only, not the data file.
Change auto-increment value for a columnYesNoYesYesModifies a value stored in memory, not the data file.
Add a foreign key constraintYes*No*YesYesTo avoid copying the table, disable foreign_key_checks during constraint creation.
Drop a foreign key constraintYesNoYesYesThe foreign_key_checks option can be enabled or disabled.
Rename a columnYes*No*Yes*YesTo allow concurrent DML, keep the same data type and only change the column name.
Add a columnYesYesYes*YesConcurrent DML is not allowed when adding an auto-increment column. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Drop a columnYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Reorder columnsYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change ROW_FORMAT propertyYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change KEY_BLOCK_SIZE propertyYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make column NULLYesYesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Make column NOT NULLYes*YesYesYesWhen SQL_MODE includes strict_all_tables or strict_all_tables, the operation fails if the column contains any nulls. Although ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation.
Change data type of columnNoYesNoYes 
Add primary keyYes*YesYesYesAlthough ALGORITHM=INPLACE is allowed, the data is reorganized substantially, so it is still an expensive operation. ALGORITHM=INPLACE is not allowed under certain conditions if columns have to be converted to NOT NULL. See Example 14.9, “Creating and Dropping the Primary Key”.
Drop primary key and add anotherYesYesYesYesALGORITHM=INPLACE is only allowed when you add a new primary key in the same ALTER TABLE; the data is reorganized substantially, so it is still an expensive operation.
Drop primary keyNoYesNoYesRestrictions apply when you drop a primary key primary key without adding a new one in the same ALTER TABLE statement.
Convert character setNoYesNoYesRebuilds the table if the new character encoding is different.
Specify character setNoYesNoYesRebuilds the table if the new character encoding is different.
Rebuild with FORCE optionYesYesYesYesUses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=COPY is used if old_alter_table=1 or mysqld --skip-new option is enabled. Table rebuild using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes.
Rebuild with null ALTER TABLE ... ENGINE=INNODBYesYesYesYesUses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=COPY is used if old_alter_table=1 or mysqld --skip-new option is enabled. Table rebuild using online DDL (ALGORITHM=INPLACE) is not supported for tables with FULLTEXT indexes.
Set table-level persistent statistics options (STATS_PERSISTENT, STATS_AUTO_RECALC STATS_SAMPLE_PAGES)YesNoYesYesModifies .frm file only, not the data file.

The following sections shows the basic syntax, and usage notes related to online DDL, for each of the major operations that can be performed with concurrent DML, in-place, or both:

Secondary Indexes

Creating and dropping secondary indexes on InnoDB tables skips the table-copying behavior, the same as in MySQL 5.5 and MySQL 5.1 with the InnoDB Plugin.

In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Previously, modifying the table while an index was being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.

Column Properties

Foreign Keys

If foreign keys are already present in the table being altered (that is, it is a child table containing any FOREIGN KEY ... REFERENCE clauses), additional restrictions apply to online DDL operations, even those not directly involving the foreign key columns:

In the same way, if a table is the parent table in a foreign key relationship, even though it does not contain any FOREIGN KEY clauses, it could wait for the ALTER TABLE to complete if an INSERT, UPDATE, or DELETE statement caused an ON UPDATE or ON DELETE action in the child table.

Notes on ALGORITHM=COPY

Any ALTER TABLE operation run with the ALGORITHM=COPY clause prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of LOCK=SHARED (allow queries but not DML). You can further restrict concurrency for such operations by specifying LOCK=EXCLUSIVE (prevent DML and queries).

Concurrent DML but Table Copy Still Required

Some other ALTER TABLE operations allow concurrent DML but still require a table copy. However, the table copy for these operations is faster than it was in MySQL 5.5 and prior.

Note

As your database schema evolves with new columns, data types, constraints, indexes, and so on, keep your CREATE TABLE statements up to date with the latest table definitions. Even with the performance improvements of online DDL, it is more efficient to create stable database structures at the beginning, rather than creating part of the schema and then issuing ALTER TABLE statements afterward.

The main exception to this guideline is for secondary indexes on tables with large numbers of rows. It is typically most efficient to create the table with all details specified except the secondary indexes, load the data, then create the secondary indexes. You can use the same technique with foreign keys (load the data first, then set up the foreign keys) if you know the initial data is clean and do not need consistency checks during the loading process.

Whatever sequence of CREATE TABLE, CREATE INDEX, ALTER TABLE, and similar statements went into putting a table together, you can capture the SQL needed to reconstruct the current form of the table by issuing the statement SHOW CREATE TABLE table\G (uppercase \G required for tidy formatting). This output shows clauses such as numeric precision, NOT NULL, and CHARACTER SET that are sometimes added behind the scenes, and you might otherwise leave out when cloning the table on a new system or setting up foreign key columns with identical type.