You should be aware of the following considerations when creating or dropping indexes using the InnoDB storage engine:
ALTER IGNORE TABLE
delete duplicate rows. This has been reported as
MySQL Bug#40344. The
t ADD UNIQUE INDEX
IGNORE keyword is ignored,
and duplicates cause failure of the operation with the following
ERROR 23000: Duplicate entry '
347' for key '
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 2.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 Section 8.7, “Better Error Handling When Dropping Indexes” for details.
MySQL 5.5 does not support efficient creation or dropping of
FOREIGN KEY constraints. Therefore, if you use
ALTER TABLE to
add or remove a
REFERENCES constraint, the child table is
copied, rather than using Fast Index Creation.
This is the User’s Guide for InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-04-13 (revision: 19994) .