MySQL create and delete index index

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

The index can be created in the CREATE TABLE statement can also be used alone, CREATE INDEX or ALTER TABLE to increase the index to the table. Delete the index can be realized by using ALTER TABLE or DROP INDEX statement.
(1)Use the ALTER TABLE statement to create index.
The syntax is as follows:
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) ;
Including general index, UNIQUE index and PRIMARY KEY index 3 index format, table_name is to increase the table name index, column_list pointed to index which columns, columns when columns are separated by a comma. Index index_name is optional, by default, MySQL will according to the first index column is assigned a name. In addition, ALTER TABLE allows you to change multiple tables in a single statement, so it can also create multiple index.
Create index of sample are 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 on the increase in the index.
To increase the general index and the UNIQUE index two. The format is as follows:
create index index_name on table_name (column_list) ;
create unique index index_name on table_name (column_list) ;

Create index of sample are 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 with the ALTER TABLE statement's meaning, not optional index name. In addition, cannot use the CREATE INDEX statement to create the PRIMARY KEY index.
(3)Drop index.
Delete the index can use ALTER TABLE or DROP INDEX statements to achieve. DROP INDEX can be used as a statement in the ALTER inside the 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 ;
Among them, the two statement on the front, delete all index index_name in table_name. But in the last statement, only used in removing PRIMARY KEY index, as a table there can be only one PRIMARY KEY index, so do not need to specify an index name. If you do not create a PRIMARY KEY index, but the table with one or more of the UNIQUE index, MySQL will delete the first UNIQUE index.
If you delete a column from the table, the index will be affected. For multiple column composite index, if you delete a column, the column will also be deleted from the index. If you delete the form index of all the columns, the index will be deleted.
Delete the index operation, as in the following code:
mysql> drop index name on table_test ;
Query OK, 2 rows affected (0.08 sec)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Baird at December 02, 2013 - 2:33 AM