DROP INDEX

This MySQL statement deletes a given index from a MySQL table. It's synonymous with the MySQL statement ALTER TABLE...DROP INDEX....

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

DROP INDEX index ON table

Explanation

This MySQL statement deletes a given index from a MySQL table. It's synonymous with the MySQL statement ALTER TABLE...DROP INDEX...—see page of our MySQL documentation for more details and options for dropping indexes from a table.

Examples

To determine the name of a particular index, we'll use the MySQL statement, SHOW INDEXES:

SHOW INDEXES FROM clients \G

*************************** 1. row ***************************
       Table: clients
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: client_id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 1
 Column_name: client_name
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 3. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 2
 Column_name: client_city
   Collation: A
 Cardinality: NULL
    Sub_part: 5
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:

The preceding results show three rows, but there really are only two indexes: the primary key based on the client_id column and the client_index, which is based on the client_name and the client_city columns combined. To delete client_index, use the DROP INDEX statement:

DROP INDEX client_index ON clients;

Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

The client_index index was succesfully dropped from the clients table. If you run SHOW INDEXES again, the results will list only the primary key.