Summary of MySQ index operation commands (detailed description of create, recreate, query, and delete index commands)

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.
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows: mysql> alter table 'Article' add index 'id'; // ADD an id INDEX to the article TABLE

Or:
Copy codeThe Code is as follows: 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.
Copy codeThe Code is as follows: mysql> repair table 'table _ name' QUICK;

3. query data table Indexes

MySQL queries table indexes in two command forms:

Copy codeThe Code is as follows: mysql> show index from 'table _ name ';

Or:
Copy codeThe Code is as follows: mysql> SHOW keys FROM 'table _ name ';

The result obtained by running the preceding command is as follows. The meaning of each column in the result is described below.

Copy codeThe Code is as follows:
The name of the Table.
Non_unique is 0 if the index cannot contain duplicate words. If yes, it is 1.
The name of the Key_name index.
The column serial number in the Seq_in_index index, starting from 1.
Column_name column name.
The way in which the Collation column is stored in the index. In MySQL, there are values 'A' (ascending) or NULL (unclassified ).
The number of unique values in the Cardinality index. You can update analyze table or myisamchk-a by running analyze table. The base number is counted based on the statistical data stored as an integer. Therefore, this value is not required to be accurate even for small tables. The larger the base, the larger the number of machines that MySQL uses the index when performing the union operation.
Sub_part: If a column is partially indexed, it is the number of indexed characters. If the entire column is indexed, the value is NULL.
Packed indicates how the keywords are compressed. If it is not compressed, It is NULL.
Null if the column contains NULL, YES is included. If NO, the column contains NO.
Index_type used index methods (BTREE, FULLTEXT, HASH, RTREE ).
Comment more comments.

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:
Copy codeThe Code 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.