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 Plugin, 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 Plugin. However, adding or dropping a secondary index with the InnoDB Plugin 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.
Because the ability to create and drop indexes does not require use of a new on-disk file format, it is possible to temporarily use the InnoDB Plugin to create or drop an index, and then fall back to using the standard built-in InnoDB in MySQL for normal operations if you wish. See Chapter 11, Downgrading from the InnoDB Plugin for more information.