MySQL index Operations Command (CREATE INDEX, rebuild index, query index, delete index)

1. CREATE index

The creation of the index can be done in the CREATE TABLE statement, or you can add indexes to the table separately with the CREATE INDEX or ALTER TABLE. The following command statements show how to create a primary key index (PRIMARY key), a federated Index (UNIQUE), and a method of the normal index (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 ID index to article table

Or:

Mysql>alter table ' article ' Add index (' id ', ' order_id '); Add ID index to Article table, ORDER_ID Index

1. Rebuilding index command

mysql> REPAIR TABLE tbl_name QUICK;

2, Query data table index
Mysql> show INDEX from Tbl_name;

3, CREATE INDEX (PRIMARY key,index,unique) support to create primary key index, federated Index and normal index command

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 index (PRIMARY key,index,unique)

Support for deleting primary key indexes, federated indexes, and common index commands

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);

Where tbl_name represents the data table name, Index_name represents the index name, and the column list represents the field list

The

Deletes the index index_name in table_name in the previous three statements. In the last statement, it is only used in the deletion of the primary key index, because a table can have only one primary key index, so you can also not specify an index name. 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 is affected. For indexes of multiple-column combinations, if you delete one of the columns, the column is also deleted from the index. If all the columns that make up the index are deleted, the entire index is deleted.