How to delete index in mysql

Mysql method to delete the index index: 1. Use the DROP INDEX statement to delete the index index; 2. Use the ALTER TABLE statement to delete the index index.

The operating environment of this article: Windows 7 system, Mysql version 5.7.14, Dell G3 computer.

How to delete index index in mysql?

Deleting index means deleting the index that already exists in the table. It is recommended to delete unused indexes because they will slow down the update speed of the table and affect the performance of the database. For such an index, it should be deleted.

To modify the index in MySQL, you can modify the index by deleting the original index and then creating an index with the same name as needed.

Basic syntax

When the index is no longer needed, you can use the DROP INDEX statement or the ALTER TABLE statement to delete the index.

1) Use the DROP INDEX statement

Syntax format:

DROP INDEX <索引名> ON <表名>

The syntax description is as follows:

<索引名>：要删除的索引名。
<表名>：指定该索引所在的表名。

2) Use the ALTER TABLE statement

According to the syntax of the ALTER TABLE statement, this statement can also be used to delete indexes. The specific method of use is to specify part of the syntax of the ALTER TABLE statement as one of the following clauses.

DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
DROP INDEX index_name：表示删除名称为 index_name 的索引。
DROP FOREIGN KEY fk_symbol：表示删除外键。

Note: If the deleted column is part of the index, then when the column is deleted, the column will also be deleted from the index; if all columns that make up the index are deleted, then the entire index will been deleted.

Delete index

[Example 1] Delete the index in table tb_stu_info. The input SQL statement and execution result are as follows.

mysql> DROP INDEX height
    -> ON tb_stu_info;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
       Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

[Example 2] Delete the index named id in the table tb_stu_info2. The input SQL statement and execution result are as follows.

mysql> ALTER TABLE tb_stu_info2
    -> DROP INDEX height;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_stu_info2\G
*************************** 1. row ***************************
       Table: tb_stu_info2
Create Table: CREATE TABLE `tb_stu_info2` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

Recommended learning: "mysql video tutorial"

The above is the detailed content of How to delete index in mysql. For more information, please follow other related articles on the PHP Chinese website!