Understand the NOCOPY algorithm, which avoids rebuilding the clustered index for certain operations like adding secondary indexes, significantly reducing I/O.
When the ALGORITHM clause is set to NOCOPY, the supported operations are a superset of the operations that are supported when the ALGORITHM clause is set to INSTANT.
Therefore, when the ALGORITHM clause is set to NOCOPY, some operations are supported by inheritance. See the following additional pages for more information about these supported operations:
ALTER TABLE ... ADD COLUMNIn and later, InnoDB supports adding columns to a table with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
This applies to for tables.
ALTER TABLE ... DROP COLUMNIn and later, InnoDB supports dropping columns from a table with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
This applies to for tables.
ALTER TABLE ... MODIFY COLUMNThis applies to for tables.
In and later, InnoDB supports reordering columns within a table with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
InnoDB does not support modifying a column's data type with set to NOCOPY in most cases. There are a few exceptions in the cases where the operation supports having the clause set to INSTANT.
See for more information.
In and later, InnoDB supports modifying a column to allow values with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
InnoDB does not support modifying a column to not allow values with set to NOCOPY.
For example:
ENUM OptionInnoDB supports adding a new option to a column with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
SET OptionInnoDB supports adding a new option to a column with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
In and later, InnoDB supports removing from a column with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
ALTER TABLE ... ALTER COLUMNThis applies to for tables.
InnoDB supports modifying a column's value with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
InnoDB supports removing a column's value with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
ALTER TABLE ... CHANGE COLUMNInnoDB supports renaming a column with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
This applies to for tables.
ALTER TABLE ... ADD PRIMARY KEYInnoDB does not support adding a primary key to a table with set to NOCOPY.
For example:
This applies to for tables.
ALTER TABLE ... DROP PRIMARY KEYInnoDB does not support dropping a primary key with set to NOCOPY.
For example:
This applies to for tables.
ALTER TABLE ... ADD INDEX and CREATE INDEXThis applies to and for tables.
InnoDB supports adding a plain index to a table with set to NOCOPY.
This operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the clause to NONE. When this strategy is used, all concurrent DML is permitted.
For example, this succeeds:
And this succeeds:
InnoDB supports adding a index to a table with set to NOCOPY.
However, there are some limitations, such as:
Adding a index to a table that does not have a user-defined FTS_DOC_ID column will require the table to be rebuilt once. When the table is rebuilt, the system adds a hidden FTS_DOC_ID column. This initial operation will have to be performed with set to INPLACE.From that point forward, adding additional indexes to the same table will not require the table to be rebuilt, and can be set to NOCOPY.
Only one index may be added at a time when is set to NOCOPY.
This operation supports a read-only locking strategy. This strategy can be explicitly chosen by setting the clause to SHARED. When this strategy is used, read-only concurrent DML is permitted.
For example, this succeeds, but the first operation requires the table to be rebuilt set to INPLACE, so that the hidden FTS_DOC_ID column can be added:
And this succeeds in the same way as above:
But this second command fails, because only one index can be added at a time:
InnoDB supports adding a index to a table with set to NOCOPY.
This operation supports a read-only locking strategy. This strategy can be explicitly chosen by setting the clause to SHARED. When this strategy is used, read-only concurrent DML is permitted.
For example, this succeeds:
And this succeeds in the same way as above:
ALTER TABLE ... DROP INDEX and DROP INDEXInnoDB supports dropping indexes from a table with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
This applies to and for tables.
ALTER TABLE ... ADD FOREIGN KEYInnoDB does supports adding foreign key constraints to a table with set to NOCOPY. In order to add a new foreign key constraint to a table with set to NOCOPY, the system variable needs to be set to OFF. If it is set to ON, then ALGORITHM=COPY is required.
This operation supports the non-locking strategy. This strategy can be explicitly chosen by setting the clause to NONE. When this strategy is used, all concurrent DML is permitted.
For example, this fails:
But this succeeds:
This applies to for tables.
ALTER TABLE ... DROP FOREIGN KEYInnoDB supports dropping foreign key constraints from a table with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
This applies to for tables.
ALTER TABLE ... AUTO_INCREMENT=...InnoDB supports changing a table's value with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
This applies to for tables.
ALTER TABLE ... ROW_FORMAT=...InnoDB does not support changing a table's with set to NOCOPY.
For example:
This applies to for tables.
ALTER TABLE ... KEY_BLOCK_SIZE=...InnoDB does not support changing a table's with set to NOCOPY.
For example:
This applies to for tables.
ALTER TABLE ... PAGE_COMPRESSED=1 and ALTER TABLE ... PAGE_COMPRESSION_LEVEL=...In and later, InnoDB supports setting a table's value to 1 with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
InnoDB does not support changing a table's value from 1 to 0 with set to NOCOPY.
In these versions, InnoDB also supports changing a table's value with set to NOCOPY in the cases where the operation supports having the clause is set to INSTANT.
See for more information.
This applies to and for tables.
ALTER TABLE ... DROP SYSTEM VERSIONINGInnoDB does not support dropping from a table with set to NOCOPY.
For example:
This applies to for tables.
ALTER TABLE ... DROP CONSTRAINTIn and later, InnoDB supports dropping a constraint from a table with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
This applies to for tables.
ALTER TABLE ... FORCEInnoDB does not support forcing a table rebuild with set to NOCOPY.
For example:
This applies to for tables.
ALTER TABLE ... ENGINE=InnoDBInnoDB does not support forcing a table rebuild with set to NOCOPY.
For example:
This applies to for tables.
OPTIMIZE TABLE ...InnoDB does not support optimizing a table with set to NOCOPY.
For example:
This applies to for tables.
ALTER TABLE ... RENAME TO and RENAME TABLE ...InnoDB supports renaming a table with set to NOCOPY in the cases where the operation supports having the clause set to INSTANT.
See for more information.
This applies to and for tables.
do not currently support online DDL for all of the same operations that are supported for "real" columns.
See for more information on the limitations.
This page is licensed: CC BY-SA / Gnu FDL
CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) ROW_FORMAT=REDUNDANT;
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab MODIFY COLUMN c VARCHAR(50) NOT NULL;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACECREATE OR REPLACE TABLE tab (
a INT,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION sql_mode='STRICT_TRANS_TABLES';
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD PRIMARY KEY (a);
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACECREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab DROP PRIMARY KEY;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPYCREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD INDEX b_index (b);
Query OK, 0 rows affected (0.009 sec)CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='NOCOPY';
CREATE INDEX b_index ON tab (b);
Query OK, 0 rows affected (0.009 sec)CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.043 sec)
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c);
Query OK, 0 rows affected (0.017 sec)CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='INPLACE';
CREATE FULLTEXT INDEX b_index ON tab (b);
Query OK, 0 rows affected (0.048 sec)
SET SESSION alter_algorithm='NOCOPY';
CREATE FULLTEXT INDEX c_index ON tab (c);
Query OK, 0 rows affected (0.016 sec)CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50),
d VARCHAR(50)
);
SET SESSION alter_algorithm='INPLACE';
ALTER TABLE tab ADD FULLTEXT INDEX b_index (b);
Query OK, 0 rows affected (0.041 sec)
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD FULLTEXT INDEX c_index (c), ADD FULLTEXT INDEX d_index (d);
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPYCREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c GEOMETRY NOT NULL
);
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ADD SPATIAL INDEX c_index (c);
Query OK, 0 rows affected (0.005 sec)CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c GEOMETRY NOT NULL
);
SET SESSION alter_algorithm='NOCOPY';
CREATE SPATIAL INDEX c_index ON tab (c);
Query OK, 0 rows affected (0.005 sec)CREATE OR REPLACE TABLE tab1 (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50),
d INT
);
CREATE OR REPLACE TABLE tab2 (
a INT PRIMARY KEY,
b VARCHAR(50)
);
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPYCREATE OR REPLACE TABLE tab1 (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50),
d INT
);
CREATE OR REPLACE TABLE tab2 (
a INT PRIMARY KEY,
b VARCHAR(50)
);
SET SESSION foreign_key_checks=OFF;
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab1 ADD FOREIGN KEY tab2_fk (d) REFERENCES tab2 (a);
Query OK, 0 rows affected (0.011 sec)CREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) ROW_FORMAT=DYNAMIC;
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ROW_FORMAT=COMPRESSED;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACECREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=4;
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab KEY_BLOCK_SIZE=2;
ERROR 1846 (0A000): ALGORITHM=NOCOPY is not supported. Reason: Changing table options requires the table to be rebuilt. Try ALGORITHM=INPLACECREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
) WITH SYSTEM VERSIONING;
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab DROP SYSTEM VERSIONING;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACECREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab FORCE;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACECREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SET SESSION alter_algorithm='NOCOPY';
ALTER TABLE tab ENGINE=InnoDB;
ERROR 1845 (0A000): ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACECREATE OR REPLACE TABLE tab (
a INT PRIMARY KEY,
b VARCHAR(50),
c VARCHAR(50)
);
SHOW GLOBAL VARIABLES WHERE Variable_name IN('innodb_defragment', 'innodb_optimize_fulltext_only');
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_defragment | OFF |
| innodb_optimize_fulltext_only | OFF |
+-------------------------------+-------+
2 rows in set (0.001 sec)
SET SESSION alter_algorithm='NOCOPY';
OPTIMIZE TABLE tab;
+---------+----------+----------+-----------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+-----------------------------------------------------------------------------+
| db1.tab | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| db1.tab | optimize | error | ALGORITHM=NOCOPY is not supported for this operation. Try ALGORITHM=INPLACE |
| db1.tab | optimize | status | Operation failed |
+---------+----------+----------+-----------------------------------------------------------------------------+
3 rows in set, 1 warning (0.002 sec)