Take the following considerations into account when creating or dropping InnoDB indexes:
During an online DDL operation that copies the table, files are written to the
temporary directory (
$TMPDIR on Unix,
on Windows, or the directory specified by the
--tmpdir configuration variable). Each temporary file is large enough
to hold one column in the new table or index, and each one is removed as soon as it is merged into the
final table or index.
TABLE statement that contains
DROP INDEX and
ADD INDEX clauses that both name the same index uses a table copy, not Fast
The table is copied, rather than using Fast Index Creation when you create an index
TEMPORARY TABLE. This has been reported as MySQL Bug #39833.
InnoDB handles error cases when users attempt to drop indexes needed for foreign keys. See section Section 184.108.40.206, "Better Error Handling when Dropping Indexes" for details.
LOCK=NONE is not allowed if there are
ON...SET NULL constraints on the table.
During each online DDL
TABLE statement, regardless of the
LOCK clause, there are brief
periods at the beginning and end requiring an exclusive lock on the table
(the same kind of lock specified by the
LOCK=EXCLUSIVE clause). Thus, an
online DDL operation might wait before starting if there is a long-running transaction performing
inserts, updates, deletes, or
SELECT ... FOR UPDATE on that table; and an
online DDL operation might wait before finishing if a similar long-running transaction was started while
ALTER TABLE was in progress.
TABLE for an
InnoDB table is mapped to an
ALTER TABLE operation to rebuild the table and update index statistics
and free unused space in the clustered index. This operation does not use fast index creation. Secondary
indexes are not created as efficiently because keys are inserted in the order they appeared in the