Drop an index

Maintenance costs increase with the number of indexes. We recommend that you drop unnecessary indexes.

Syntax

In MySQL mode, you can use either of the following statements to delete an index:

Syntax 1

DROP INDEX index_name idx on table_name;

Parameters

Syntax 2

ALTER TABLE table_name DROP key|index index_name ;

Examples

Example 1

1. View the index on the test table.

   obclient> SHOW INDEX FROM test;
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | test  |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | test  |          1 | idx      |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | test  |          1 | idx      |            2 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   3 rows in set
   

2. Drop the idx index from the test table.

   obclient> DROP INDEX idx on test;
   Query OK, 0 rows affected
   

3. View the index on the test table.

   obclient> SHOW INDEX FROM test;
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | test  |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set
   

Example 2

1. View the index on the t_idx1 table.

   obclient> SHOW INDEX FROM t_idx1;
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | t_idx1 |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   | t_idx1 |          1 | idx      |            1 | date        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   2 rows in set
   

2. Drop the index from the t_idx1 table.

   obclient> ALTER TABLE t_idx1 DROP INDEX idx ;
   Query OK, 0 rows affected
   

3. View the index on the t_idx1 table again.

   obclient> SHOW INDEX FROM t_idx1;
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   | t_idx1 |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
   +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
   1 row in set