Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.6Mb
PDF (A4) - 31.7Mb
PDF (RPM) - 30.7Mb
HTML Download (TGZ) - 7.7Mb
HTML Download (Zip) - 7.7Mb
HTML Download (RPM) - 6.6Mb
Man Pages (TGZ) - 189.5Kb
Man Pages (Zip) - 303.9Kb
Info (Gzip) - 3.0Mb
Info (Zip) - 3.0Mb
Excerpts from this Manual

14.13.1 Online DDL Overview

The online DDL feature enhances many DDL operations that formerly required a table copy or blocked DML operations on the table, or both. Table 14.8, “Online Status for DDL Operations” shows how the online DDL feature applies to each DDL statement.

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.13.7, “Online DDL for Partitioned Tables”.

Some factors affect the performance, space usage, and semantics of online DDL operations. For more information, see Section 14.13.8, “Online DDL Limitations”.

  • The In-Place? column shows which operations permit the ALGORITHM=INPLACE clause.

  • The Rebuilds Table? column shows which operations rebuild the table. For operations that use the INPLACE algorithm, the table is rebuilt in place. For operations that do not support the INPLACE algorithm, the table copy method is used to rebuild the table.

  • The Permits Concurrent DML? column shows which operations are performed fully online. You can specify LOCK=NONE to assert that concurrent DML is permitted during the DDL operation. MySQL automatically permits concurrent DML when possible.

    Concurrent queries are permitted during all online DDL operations. You can specify LOCK=SHARED to assert that concurrent queries are permitted during a DDL operation. MySQL automatically permits concurrent queries when possible.

  • The Notes column provides additional information and explains exceptions and dependencies related to the Yes/No values of other columns. An asterisk indicates an exception or dependency.

Table 14.8 Online Status for DDL Operations

OperationIn-Place?Rebuilds Table?Permits Concurrent DML?Only Modifies Metadata?Notes
CREATE INDEX, ADD INDEXYes*No*YesNoRestrictions apply for FULLTEXT indexes; see next row.
ADD FULLTEXT INDEXYes*No*NoNoAdding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent FULLTEXT indexes may be added on the same table without rebuilding the table.
DROP INDEXYesNoYesYesOnly modifies table metadata.
OPTIMIZE TABLEYes*YesYesNoPerformed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with FULLTEXT indexes.
Set column default valueYesNoYesYesOnly modifies table metadata.
Change auto-increment valueYesNoYesNo*Modifies a value stored in memory, not the data file.
Add foreign key constraintYes*NoYesYesThe INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.
Drop foreign key constraintYesNoYesYesforeign_key_checks can be enabled or disabled.
Rename columnYesNoYes*YesTo permit concurrent DML, keep the same data type and only change the column name.
Add columnYesYesYes*NoConcurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.
Drop columnYesYesYesNoData is reorganized substantially, making it an expensive operation.
Reorder columnsYesYesYesNoData is reorganized substantially, making it an expensive operation.
Change ROW_FORMAT propertyYesYesYesNoData is reorganized substantially, making it an expensive operation.
Change KEY_BLOCK_SIZE propertyYesYesYesNoData is reorganized substantially, making it an expensive operation.
Make column NULLYesYes*YesNoRebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Make column NOT NULLYes*Yes*YesNoRebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.7, “ALTER TABLE Syntax”. Data is reorganized substantially, making it an expensive operation.
Change column data typeNoYesNoNoOnly supports ALGORITHM=COPY
Add primary keyYes*Yes*YesNoRebuilds the table in place. Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL.
Drop primary key and add anotherYesYesYesNoData is reorganized substantially, making it an expensive operation.
Drop primary keyNoYesNoNoOnly ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.
Convert character setNoYes*NoNoRebuilds the table if the new character encoding is different.
Specify character setNoYes*NoNoRebuilds the table if the new character encoding is different.
Rebuild with FORCE optionYes*YesYesNoUses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
null rebuild using ALTER TABLE ... ENGINE=INNODBYes*YesYesNoUses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.
Set STATS_PERSISTENT, STATS_AUTO_RECALC, STATS_SAMPLE_PAGES persistent statistics optionsYesNoYesYesOnly modifies table metadata.

The sections that follow provide basic syntax and usage notes for various online DDL operations.

Adding or Dropping Secondary Indexes

  • Adding a secondary index:

    CREATE INDEX name ON table (col_list);
    ALTER TABLE table ADD INDEX name (col_list);
  • Dropping a secondary index:

    DROP INDEX name ON table;
    ALTER TABLE table DROP INDEX name;

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.13.8, “Online DDL Limitations”).

Creating and dropping secondary indexes on InnoDB tables skips the table-copying behavior.

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 is being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.

Online DDL support for adding secondary indexes 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.

Modifying Column Properties

  • Modify the default value for a column:

    ALTER TABLE tbl ALTER COLUMN col SET DEFAULT literal;
    ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT;

    The default values for columns are stored in the .frm file for the table, not the InnoDB data dictionary.

  • Changing the auto-increment value for a column:

    ALTER TABLE table AUTO_INCREMENT=next_value;

    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:

    ALTER TABLE tbl CHANGE old_col_name new_col_name datatype;

    When you keep the same data type and [NOT] NULL attribute, only changing the column name, this operation can always be performed online.

    You can also rename a column that is part of a foreign key constraint. 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 use ALGORITHM=COPY behind the scenes, the ALTER TABLE statement fails.

Adding or Dropping Foreign Keys

  • Adding or dropping a foreign key constraint:

    ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1) 
      REFERENCES tbl2(col2) referential_actions;
    ALTER TABLE tbl DROP FOREIGN KEY fk_name;

    Dropping a foreign key can be performed online with the foreign_key_checks option enabled or disabled. Creating a foreign key online requires foreign_key_checks to be disabled.

    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 CONSTRAINT clause for each foreign key:

    SHOW CREATE TABLE table\G

    Or, query the INFORMATION_SCHEMA.TABLE_CONSTRAINTS table and use the CONSTRAINT_NAME and CONSTRAINT_TYPE columns to identify the foreign key names.

    You can also drop a foreign key and its associated index in a single statement:

    ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

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

  • An ALTER TABLE on the child table could wait for another transaction to commit, if a change to the parent table caused associated changes in the child table through an ON UPDATE or ON DELETE clause using the CASCADE or 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 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.

Maintaining CREATE TABLE Statements

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, which you may want to leave out when cloning the table on a new system or setting up foreign key columns with identical type.


User Comments
  Posted by Rolf Martin-Hoster on July 10, 2013
It should be noted that while compressed innodb tables are updated in place, this an ADD INDEX can still take quite a long time time.
Sign Up Login You must be logged in to post a comment.