Introduction to using MySQL commands to create, delete, and query indexes

MySQL database tables can create, view, rebuild and delete indexes, which can improve query speed. Indexes are classified into common indexes and unique indexes; there are new indexes, modified indexes, and deleted indexes. However, indexes cannot be created everywhere and need to be created based on specific conditions. The following example illustrates the process of index creation and destruction. The operation is as follows:

Familiarity with MySQL commands can help you perform various database operations conveniently and flexibly. This article mainly explains how to use commands to operate MySQL indexes, including creating indexes, rebuilding indexes, querying indexes, and deleting indexes. In the following examples, `table_name` indicates the table name, `index_name` indicates the index name, and column list indicates the field list (such as: `id`, `order_id`).

1. Create an index

Indexes can be created in the CREATE TABLE statement, or by using CREATE INDEX or ALTER TABLE alone to add indexes to a table. The following commands show how to create a primary key index (PRIMARY KEY), a joint index (UNIQUE), and a common 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 the article table

or:

mysql>ALTER TABLE `article` ADD INDEX (`id`,`order_id`); //Add id index and order_id index to the article table

2. Rebuild the index

Rebuilding indexes is often used during routine database maintenance operations. After the database has been running for a long time, the index may be damaged and needs to be rebuilt. Re-indexing data can improve retrieval efficiency.

mysql> REPAIR TABLE `table_name` QUICK;

3. Query data table index

mysql> SHOW INDEX FROM `table_name`;

4. Delete index

Deleting an index can be done using the ALTER TABLE or DROP INDEX statements. DROP INDEX can be processed as a single statement inside ALTER TABLE, with 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 to delete the PRIMARY KEY index. Because a table can only have one PRIMARY KEY index, the index name does not need to be specified. If no PRIMARY KEY index is created, but the table has one or more UNIQUE indexes, MySQL deletes the first UNIQUE index. If you remove a column from a table, the index will be affected. For an index with multiple columns, if you delete one of the columns, the column will also be deleted from the index. If you drop all the columns that make up an index, the entire index will be dropped.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links