MySQL index Operation Command (create index, re-index, query index, delete index) Summary

This article mainly summarizes the MySQL index operation methods, including creating indexes, rebuilding indexes, querying indexes, and deleting indexes. In the following example, 'table _ name' indicates the data table name, 'index _ name' indicates the index name, and column list indicates the Field list (for example, 'id ', 'order _ id ').

1. Create an index

You can CREATE an INDEX in the create table statement, or use the create index or alter table statement to add an INDEX to the TABLE. The following command statements demonstrate how to create a primary key index, a unique index, and an INDEX.

Mysql> alter table 'table _ name' ADD index'index _ name' (column list );

Mysql> alter table 'table _ name' add unique 'index _ name' (column list );

Mysql> alter table 'table _ name' add primary key 'index _ name' (column list );

Mysql> create index 'index _ name' ON 'table _ name' (column_list );

Mysql> create unique index 'index _ name' ON 'table _ name' (column_list );

For example:

Mysql> alter table 'Article' add index 'id'; // ADD an id INDEX to the article TABLE

Or:

Mysql> alter table 'Article' add index ('id', 'order _ id'); ADD id INDEX and order_id INDEX to the article TABLE

2. Re-Indexing

Rebuilding indexes is often used in routine database maintenance operations. After the database has been running for a long time, the indexes may be damaged, and reconstruction is required. Re-indexing data can improve the retrieval efficiency.

Mysql> repair table 'table _ name' QUICK;

3. query data table Indexes

Mysql> show index from 'table _ name ';

For details about how to query data table indexes, refer to this article: mysql Command for querying table Indexes

4. delete an index

You can use the alter table or drop index statement to delete an INDEX. Drop index can be processed as a statement in alter table. The format is as follows:

Mysql> DROP index 'index _ name' ON 'table _ name' (column list );

Mysql> alter table 'table _ name' DROP index'index _ name' (column list );

Mysql> alter table 'table _ name' drop unique 'index _ name' (column list );

Mysql> alter table 'table _ name' drop primary key 'index _ name' (column list );

In the first three statements, the index index_name in table_name is deleted. In the last statement, the primary key index is used only to delete the primary key index. Because a table may only have one primary key index, the index name is not specified. If the primary key index is not created, but the table has one or more UNIQUE indexes, MySQL deletes the first UNIQUE index. If you delete a column from the table, the index will be affected. If you delete a column in an index with multiple columns, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

Articles you may be interested in

• Shoulder, back muscle group exercise-Bring Up-bend up rowing-Move up-Stand Up-Stand up weight bend up-Sit posture neck pull down-bend upright hold up-neck front wide hold up
• Common search engine commands and common search engine syntax
• Analyze the reasons why Baidu index volume is decreasing and how to increase Baidu index volume
• Summary of the causes and solutions for slow mysql Server Query
• Edit the jQuery select drop-down box (add, delete, modify)
• Principles and Methods for database index creation
• Note the following when querying strings with single quotes and inserting strings with single quotes in Mysql:
• Mysql Command for querying table Indexes