Secondary Indexes

Secondary Indexes
• Create secondary indexes: CREATE INDEX name ON table (col_list) or ALTER TABLE
table ADD INDEX name (col_list). (Creating a a FULLTEXT index still requires locking the
table.)
• Drop secondary indexes: DROP INDEX name ON table; or ALTER TABLE table DROP INDEX
name
Creating and dropping secondary indexes on InnoDB tables skips the table-copying behavior, the
same as in MySQL 5.5 and MySQL 5.1 with the InnoDB Plugin.
In MySQL 5.6 and higher, the table remains available for read and write operations while the index
is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all
transactions that are accessing the table are completed, so that the initial state of the index reflects the
most recent contents of the table. Previously, modifying the table while an index was being created or
dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on
the table.
... zobacz całą notatkę