Shulou(Shulou.com)05/31 Report--

How do I create, delete, and query indexes in MySQL? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

Familiar with the use of MySQL commands can easily and flexibly perform a variety of database operations; this article is mainly on how to use commands to operate MySQL indexes, including creating indexes, rebuilding indexes, querying indexes, and deleting indexes. In the examples listed below, `table_ name` represents the data table name, `index_ name` represents the index name, and column list represents the field list (such as `id`, `order_ id`).

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`

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 `index_ 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.

This is the answer to the question about how to create, delete and query the index in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

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.