In MySQL versions up to 5.0, adding or dropping an index on a
table with existing data can be very slow if the table has many
CREATE INDEX and
DROP INDEX commands work by
creating a new, empty table defined with the requested set of
indexes. It then copies the existing rows to the new table
one-by-one, updating the indexes as it goes. Inserting entries
into the indexes in this fashion, where the key values are not
sorted, requires random access to the index nodes, and is far from
optimal. After all rows from the original table are copied, the
old table is dropped and the copy is renamed with the name of the
Beginning with version 5.1, MySQL allows a storage engine to create or drop indexes without copying the contents of the entire table. The standard built-in InnoDB in MySQL version 5.1, however, does not take advantage of this capability. With the InnoDB storage engine, however, users can in most cases add and drop indexes much more efficiently than with prior releases.
In InnoDB, the rows of a table are stored in a clustered (or primary key) index, forming what some database systems call an “index-organized table”. Changing the clustered index requires copying the data, even with the InnoDB storage engine. However, adding or dropping a secondary index with the InnoDB storage engine is much faster, since it does not involve 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.
No syntax changes are required in the
CREATE INDEX or
DROP INDEX commands. However, there are some considerations of
which you should be aware (see
Section 2.6, “Limitations of 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) .