Take the following considerations into account when creating or dropping InnoDB indexes:
During index creation, files are written to the temporary directory (
%TEMP%on Windows, or the value of the
--tmpdirconfiguration variable). Each temporary file is large enough to hold one column that makes up the new index, and each one is removed as soon as it is merged into the final index.
ALTER TABLEstatement that contains
ADD INDEXclauses 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
TEMPORARY TABLE. This has been reported as MySQL Bug #39833.
To avoid consistency issues between the InnoDB data dictionary and the MySQL data dictionary, the table is copied, rather than using Fast Index Creation when you use the
ALTER TABLE ... RENAME COLUMNsyntax.
ALTER IGNORE TABLEdoes not delete duplicate rows. This has been reported as MySQL Bug #40344. The
tADD UNIQUE INDEX
IGNOREkeyword is ignored. If any duplicate rows exist, the operation fails with the following error message:
ERROR 23000: Duplicate entry '347' for key 'pl'
As noted above, a newly-created index contains only information about data current at the time the index was created. Therefore, you should not run queries in a transaction that might use a secondary index that did not exist at the beginning of the transaction. There is no way for InnoDB to access “old” data that is consistent with the rest of the data read by the transaction. See the discussion of locking in Section 14.16.4, “Concurrency Considerations for Fast Index Creation”.
Prior to InnoDB storage engine 1.0.4, unexpected results could occur if a query attempts to use an index created after the start of the transaction containing the query. If an old transaction attempts to access a “too new” index, InnoDB storage engine 1.0.4 and later reports an error:
ERROR HY000: Table definition has changed, please retry transaction
As the error message suggests, committing (or rolling back) the transaction, and restarting it, cures the problem.
InnoDB storage engine 1.0.2 introduces some improvements in error handling when users attempt to drop indexes. See Section B.3, “Server Error Codes and Messages” for information related to errors
MySQL 5.5 does not support efficient creation or dropping of
FOREIGN KEYconstraints. Therefore, if you use
ALTER TABLEto add or remove a
REFERENCESconstraint, the child table is copied, rather than using Fast Index Creation.
OPTIMIZE TABLEfor an
InnoDBtable is mapped to an
ALTER TABLEoperation 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.