Purpose
This statement is used to drop an index on a table. When there are too many indexes, the maintenance overhead increases, so it is necessary to drop unnecessary indexes.
Notice
- After an index is dropped, it can no longer be used to optimize query performance. Proceed with caution.
- Dropping an index does not affect the data in the table.
- You can drop various types of indexes, including regular indexes, unique indexes, and full-text indexes.
Syntax
DROP INDEX index_name ON table_name;
Parameters
| Parameter | Description |
|---|---|
| index_name | The name of the index to be dropped. |
| table_name | The name of the table to which the index belongs. |
Examples
- Create a table and an index, then drop the index.
obclient> CREATE TABLE test(c1 INT PRIMARY KEY, c2 INT, c3 INT);
obclient> CREATE INDEX test_index ON test(c2);
obclient> SHOW INDEX FROM test;
The query result is as follows:
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| 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 | test_index | 1 | c2 | A | NULL | NULL | NULL | YES | BTREE | available | | YES |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
2 rows in set
Drop the index:
obclient> DROP INDEX test_index ON test;
obclient> SHOW INDEX FROM test;
The query result is as follows:
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| 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
- Drop a unique index. After a unique index is dropped, the uniqueness it enforces will no longer be maintained, allowing duplicate values to be inserted.
obclient> CREATE TABLE test(c1 INT PRIMARY KEY, c2 INT, UNIQUE INDEX idx(c2));
obclient> INSERT INTO test(c1, c2) VALUES(1, 1);
obclient> DROP INDEX idx ON test;
obclient> INSERT INTO test(c1, c2) VALUES(2, 1);
- Drop a full-text index.
obclient> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ctx(title, content)
);
obclient> DROP INDEX ctx ON articles;