Table of Contents for

MySQL in a Nutshell, 2nd Edition

This statement deletes a given index from a table. It’s synonymous with ALTER TABLE...DROP INDEX.... See the section on ALTER TABLE: DROP index clauses” under ALTER TABLE earlier in this chapter for more details and options for dropping indexes from a table.

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

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 are really 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 is successfully dropped from the clients table. If you run SHOW INDEXES again, the results will list only the primary key.