DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name

DROP INDEX drops the index named index_name from the table tbl_name. This statement is mapped to an ALTER TABLE statement to drop the index. See Section 12.1.7, “ALTER TABLE Syntax”.

Beginning with MySQL 5.1.7, indexes on variable-width columns are dropped online; that is, dropping the indexes does not require any copying of the table. For NDBCLUSTER tables, the table is not locked against access from other MySQL Cluster API nodes, although it is locked against other operations on the same API node for the duration of the online operation. This is done automatically by the server whenever it determines that it is possible to do so; you do not have to use any special SQL syntax or server options to cause it to happen.

In standard MySQL 5.1 releases, it is not possible to override the server when it determines that an index is to be dropped online. In MySQL Cluster, beginning with MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.3, you can drop indexes offline (which causes the table to be locked for all API nodes in the cluster) using the OFFLINE keyword. The rules and limitations governing online DROP OFFLINE INDEX and DROP ONLINE INDEX are the same as for ALTER OFFLINE TABLE ... DROP INDEX and ALTER ONLINE TABLE ... DROP INDEX. You cannot cause the online dropping of an index that would normally be dropped offline by using the ONLINE keyword (if it is not possible to perform the DROP operation online, then the ONLINE keyword is ignored). For more information, see Section 12.1.7, “ALTER TABLE Syntax”.