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

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

1. Re-indexing command

Mysql> repair table tbl_name QUICK;

2. query data table Indexes
Mysql> show index from tbl_name;

 
3. Create an INDEX (primary key, INDEX, UNIQUE) supports creating a primary key index, joint INDEX, and common INDEX commands.

 
Mysql> alter table tbl_name add index index_name (column list );
Mysql> alter table tbl_name add unique index_name (column list );
Mysql> alter table tbl_name add primary key index_name (column list );

4. delete an INDEX (primary key, INDEX, UNIQUE)

Supports commands for deleting primary key indexes, joint indexes, and common indexes.

 
Mysql> alter table tbl_name drop index index_name (column list );
Mysql> alter table tbl_name drop unique index_name (column list );
Mysql> alter table tbl_name drop primary key index_name (column list );

Tbl_name indicates the data table name, index_name indicates the index name, and column list indicates the field 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.