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
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.
ALTER TABLE statement that
DROP INDEX and
INDEX clauses that both name the same index uses a
table copy, not Fast Index Creation.
The table is copied, rather than using Fast Index Creation
when you create an index on a
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 220.127.116.11, “Better Error Handling when Dropping Indexes” for details.
ALTER TABLE clause
LOCK=NONE is not allowed if there are
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,
SELECT ... FOR UPDATE on that table; and
an online DDL operation might wait before finishing if a
similar long-running transaction was started while the
ALTER TABLE was in progress.
OPTIMIZE 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 primary key.