With MySQL 5.5 and higher, or MySQL 5.1 with the InnoDB Plugin,
creating and dropping
secondary indexes for
InnoDB tables is much faster than before. Historically, adding or
dropping an index on a table with existing data could be very
CREATE INDEX and
DROP INDEX statements worked by
creating a new, empty table defined with the requested set of
indexes, then copying the existing rows to the new table
one-by-one, updating the indexes as the rows are inserted. After
all rows from the original table were copied, the old table was
dropped and the copy was renamed with the name of the original
The performance speedup for fast index creation applies to secondary indexes, not to the primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the primary key, forming what some database systems call an “index-organized table”. Because the table structure is so closely tied to the primary key, redefining the primary key still requires copying the data.
This new mechanism also means that you can generally speed the overall process of creating and loading an indexed table by creating the table with only the clustered index, and adding the secondary indexes after the data is loaded.
Although no syntax changes are required in the
CREATE INDEX or
DROP INDEX commands, some factors
affect the performance, space usage, and semantics of this
operation (see Section 14.14.6, “Limitations of Fast Index Creation”).