Examples of using MySQL covering indexes

What is a covering index?

Creating an index that includes all the fields used in the query is called a covering index.

With a covering index, MySQL only needs to use the index to find and return the data required for the query, without having to perform a table return operation after processing the data using the index.

Covering indexes can complete the query work at one time, effectively reducing IO and improving query efficiency.

Usage Examples

Query statement:

SELECT col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

Create a single-column index:

ALTER TABLE `test_table` ADD INDEX `idx_col1`(`col1`) USING BTREE;

Analysis query:

EXPLAIN SELECT SQL_NO_CACHE col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

Result: After creating an index for col1, type is ref and the idx_col1 index is used.

Modify the index and create a joint index based on the query statement:

ALTER TABLE `test_table` DROP INDEX `idx_col1`;
ALTER TABLE `test_table` ADD INDEX `idx_col1_col2_col3`(`col1`, `col2`, `col3`) USING BTREE;

Analysis query:

EXPLAIN SELECT SQL_NO_CACHE col2, col3 FROM test_table WHERE col1 = xxx ORDER BY col2;

Result: After the joint index is created, type is ref, the idx_col1_col2_col3 index is used, and Extra is Using index, indicating that a covering index is used.

The difference between mysql covering index and joint index

A covering index means that the queried columns can be directly extracted through the index, such as only querying the columns of the primary key list! Or query all columns of the joint index or some columns starting from the left (note that they are in order)!

A joint index does not necessarily retrieve all data from the index; this depends on the column you are querying. For example, the method of select * from table where ××××××; is unlikely to be a covering index. Therefore, if the columns you query can use the joint index, and the columns you query can be obtained through the joint index, for example, you only query the column where the joint index is located or part of the columns starting from the left, this is equivalent to a covering index. Usually, in order to allow the query to use the covering index, the multiple columns of data to be queried are set as a joint index.

Summarize

This is the end of this article about the use of MySQL covering indexes. For more relevant MySQL covering index content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!