Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief talk about mysql database index operation. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on mysql database index operation can bring you some practical help.

1. Create an index

The index can be created in a CREATE TABLE statement, or you can add an index to the table using CREATE INDEX or ALTER TABLE alone. The following command statement shows how to create a primary key index (PRIMARY KEY), a federated index (UNIQUE), and a 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 `roomle`ADD INDEX `id`; / / add an id index to the article table

Or:

Mysql > ALTER TABLE `roomle` ADD INDEX (`id`, `order_ id`); add id index and order_id index to article table

2. Rebuild the index

Re-indexing is often used in regular database maintenance operations. After the database has been running for a long time, the index may be corrupted, so it needs to be rebuilt. Re-indexing the data can improve the retrieval efficiency.

Mysql > REPAIR TABLE `table_ name` QUICK

3. Query data table index

Mysql > SHOW INDEX FROM `table_ name`

About querying the data table index, please refer to this site article: mysql query table index command for detailed explanation

4. Delete the index

Deleting an index can be done using ALTER TABLE or DROP INDEX statements. DROP INDEX can be processed as a statement within ALTER TABLE in the following format:

Mysql > DROP index `index_ name` ON `table_ name` (column list)

Mysql > ALTER TABLE `table_ name` DROP INDEX `index_ name` (column list)

Mysql > ALTER TABLE `table_ name` DROP UNIQUE `index_ name` (column list)

Mysql > ALTER TABLE `table_ name` DROP PRIMARY KEY `index_ name` (column list)

In the previous three statements, the index index_name in table_name is deleted. In the last statement, it is only used when dropping the PRIMARY KEY index, because a table can only have one PRIMARY KEY index, so the index name can also be left unspecified. If no PRIMARY KEY index is 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 an index with a combination of columns, if you delete one of the 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.

Mysql database index operation will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.