Create and delete indexes in mysql _ MySQL

Mysql creates an index and deletes an index bitsCN.com

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. You can use alter table or drop index statements to delete indexes.
(1) use the alter table statement to create an index.
Syntax:
Alter table table_name add index index_name (column_list );
Alter table table_name add unique (column_list );
Alter table table_name add primary key (column_list );
There are three index creation formats: normal index, UNIQUE index, and primary key index. table_name is the name of the table to add the index. column_list indicates which columns are indexed, multiple columns are separated by commas. The index name index_name is optional and is time-saving. MySQL assigns a name based on the first index column. In addition, alter table allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.
An example of creating an index is as follows:
Mysql> alter table table_test add index index_test1 (name );
Query OK, 2 rows affected (0.08 sec)
(2) use the create index statement to add an INDEX to a table.
Two types of common indexes and UNIQUE indexes can be added. The format is as follows:
Create index index_name on table_name (column_list );
Create unique index index_name on table_name (column_list );

An example of creating an index is as follows:

Mysql> create index index_test2 on table_test (age );

Query OK, 2 rows affected (0.08 sec)

Note: table_name, index_name, and column_list have the same meaning as the alter table statement, and the index name is not optional. In addition, you cannot use the create index statement to CREATE a primary key index.
(3) 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:
Drop index index_name on table_name;
Alter table table_name drop index index_name;
Alter table table_name drop primary key;
In the preceding two 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, you do not need to specify the 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 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.
The following code deletes an index:
Mysql> drop index name on table_test;
Query OK, 2 rows affected (0.08 sec)

BitsCN.com