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

Source: Internet
Author: User
Tags create index mysql 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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.