Shulou(Shulou.com)05/31 Report--

Editor to share with you mysql how to delete the index index, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql delete index index method: 1, use DROP INDEX statement to delete index index; 2, use ALTER TABLE statement to delete index index.

This article operating environment: Windows7 system, Mysql5.7.14 version, Dell G3 computer.

To delete an index is to delete an index that already exists in the table. Unused indexes are recommended for deletion because they slow down the update speed of the table and affect the performance of the database. For such an index, it should be deleted.

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

Basic grammar

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

1) use the DROP INDEX statement

Syntax format:

DROP INDEX ON

The syntax is as follows:

The name of the index to delete Specifies the name of the table in which the index is located

2) use ALTER TABLE statement

According to the syntax of the ALTER TABLE statement, it can also be used to delete an index. The specific way to use it is to specify part of the syntax of the ALTER TABLE statement as one of the following clauses.

DROP PRIMARY KEY: means to delete the primary key in the table. A table has only one primary key, and the primary key is also an index. DROP INDEX index_name: means to delete an index named index_name. DROP FOREIGN KEY fk_symbol: means to delete a foreign key.

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

Delete index

[example 1] Delete the index in the table tb_stu_info, enter the SQL statement and the execution result is shown below.

Mysql > DROP INDEX height-> ON tb_stu_info Query OK, 0 rows affected (0.27 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE TABLE tb_stu_info\ Graph * 1. Row * * Table: tb_stu_infoCreate 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=gb23121 row in set (0.00 sec)

[example 2] Delete the index named id in the table tb_stu_info2, and the SQL statement entered and the execution result are shown below.

Mysql > ALTER TABLE tb_stu_info2-> DROP INDEX height Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > SHOW CREATE TABLE tb_stu_info2\ Graph * 1. Row * * Table: tb_stu_info2Create 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=gb23121 row in set (0.00 sec) above are all the contents of the article "how mysql deletes the index Index" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.