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
TABLE operations to avoid copying the table. Another enhancement allows
SELECT queries and
statements to proceed while the table is being altered. In MySQL 5.7,
ALTER TABLE RENAME INDEX was also enhanced to avoid table copying. This
combination of features is now known as online
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 with without any secondary indexes, then adding the secondary indexes after the data is loaded.
Although no syntax changes are required in the
DROP INDEX commands, some factors affect the performance, space usage, and
semantics of this operation (see Section 5.5.9, "Limitations of
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
5.8, "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
The "In-Place?" column shows
which operations allow the
ALGORITHM=INPLACE clause; the preferred value is
The "Copies Table?" column
shows which operations are able to avoid the expensive table-copying operation; the preferred value is
"No". This column is mostly the reverse of the
"In-Place?" column, except that a few operations
ALGORITHM=INPLACE but still involve some amount of table copying.
The "Allows Concurrent DML?"
column shows which operations can be performed fully online; the preferred value is "Yes". You can specify
assert that full concurrency is allowed during the DDL, but MySQL automatically allows this level of
concurrency when possible. When concurrent DML is allowed, concurrent queries are also always allowed.
The "Allows Concurrent Queries?" column shows which DDL operations allow
queries on the table while the operation is in progress; the preferred value is "Yes". Concurrent query is allowed during all
online DDL operations. It is shown with "Yes"
listed for all cells, for reference purposes. You can specify
to assert that concurrent queries are allowed during the DDL, but MySQL automatically allows this level
of concurrency when possible.
The "Notes" column explains any exceptions to the "yes/no" values of the other columns, such as when the answer depends on the setting of a configuration option or some other clause in the DDL statement. The values "Yes*" and "No*" indicate that an answer depends on these additional notes.
Table 5.8. Summary of Online Status for DDL Operations
|Operation||In-Place?||Copies Table?||Allows Concurrent DML?||Allows Concurrent Query?||Notes|
||Yes*||No*||Yes||Yes||Some restrictions for
||Yes||No*||No||Yes||Creating the first
|Set default value for a column||Yes||No||Yes||Yes||Modifies
|Change auto-increment value for a column||Yes||No||Yes||Yes||Modifies a value stored in memory, not the data file.|
|Add a foreign key constraint||Yes*||No*||Yes||Yes||To avoid copying the table, disable
|Drop a foreign key constraint||Yes||No||Yes||Yes||The
|Rename a column||Yes*||No*||Yes*||Yes||To allow concurrent DML, keep the same data type and only change the column name.|
|Add a column||Yes||Yes||Yes*||Yes||Concurrent DML is not allowed when adding an auto-increment
|Drop a column||Yes||Yes||Yes||Yes||Although
|Change data type of column||No||Yes||No||Yes|
|Add primary key||Yes*||Yes||Yes||Yes||Although
|Drop primary key and add another||Yes||Yes||Yes||Yes||
|Drop primary key||No||Yes||No||Yes||Restrictions apply when you drop a primary key primary key without adding a new one
in the same
|Convert character set||No||Yes||No||Yes||Rebuilds the table if the new character encoding is different.|
|Specify character set||No||Yes||No||Yes||Rebuilds the table if the new character encoding is different.|
||No||Yes||No||Yes||Acts like the
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:
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
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
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
DELETE statement on the table.
Set a default value for a column:
ALTER TABLE or
tbl ALTER COLUMN
tbl ALTER COLUMN
col DROP DEFAULT
Changing the auto-increment value for a column:
Especially in a distributed system using replication or sharding, you sometimes reset the auto-increment counter for a table to a specific value. The next row inserted into the table uses the specified value for its auto-increment column. You might also use this technique in a data warehousing environment where you periodically empty all the tables and reload them, and you can restart the auto-increment sequence from 1.
Renaming a column:
When you keep the same data type and
[NOT] NULL attribute, only
changing the column name, this operation can always be performed online.
As part of this enhancement, you can now rename a column that is part of a foreign key constraint,
which was not allowed before. The foreign key definition is automatically updated to use the new
column name. Renaming a column participating in a foreign key only works with the in-place mode of
ALTER TABLE. If you use the
ALGORITHM=COPY clause, or some other condition causes the command to
ALGORITHM=COPY behind the scenes, the
TABLE statement will fail.
ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(255);
The number of length bytes required by a
VARCHAR column must remain the same. For
VARCHAR values of 0 to 255, one length byte is required to encode the
values of 256 bytes or more, two length bytes are required. As a result, in-place
ALTER TABLE only supports increasing
VARCHAR size from 0 to 255 bytes or increasing
VARCHAR size from a value equal to or greater than 256 bytes.
ALTER TABLE does not support increasing
VARCHAR size from less than 256 bytes to a value equal to or
greater than 256 bytes. In this case, the number of required length bytes would change from 1 to 2,
which is only supported by a table copy (
Adding or dropping a foreign key constraint:
tblDROP FOREIGN KEY
If you do not know the names of the foreign key constraints on a particular table, issue the
following statement and find the constraint name in the
clause for each foreign key:
show create table
Or, query the
information_schema.table_constraints table and use the
to identify the foreign key names.
As a consequence of this enhancement, you can now also drop a foreign key and its associated index in a single statement, which previously required separate statements in a strict order:
tableDROP FOREIGN KEY
constraint, DROP INDEX
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:
Concurrent DML is disallowed during online DDL operations on such child tables. (This restriction is being evaluated as a bug and might be lifted.)
TABLE on the child table could also wait for another transaction to commit, if a change to
the parent table caused associated changes in the child table through an
ON DELETE clause using the
SET NULL parameters.
In the same way, if a table is the parent
table in a foreign key relationship, even though it does not contain any
KEY clauses, it could wait for the
TABLE to complete if an
DELETE statement caused an
ON UPDATE or
ON DELETE action in the child table.
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
(prevent DML and queries).
ALTER TABLE operations allow concurrent DML, and are faster than MySQL 5.5 and
prior: the table-copying operation is optimized, even though a table copy is still required:
Adding, dropping, or reordering columns.
Adding or dropping a primary key.
properties for a table.
Changing the nullable status for a column.
As your database schema evolves with new columns, data types, constraints, indexes, and so on, keep
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
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
\G required for tidy formatting). This output shows clauses such as
NOT NULL, and
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.