Create and delete indexes in mysql

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:



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> index_test1 (name );
Query OK, 2 rows affected (0.08 sec)

Two types of common indexes and UNIQUE indexes can be added. The format is as follows:
Create unique index index_name on table_name (column_list );

An example of creating an index is as follows:

Mysql> 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:



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> name on table_test;
Query OK, 2 rows affected (0.08 sec)