How to use index in mysql?

If the index can be used for sorting during the sorting operation, the sorting speed can be greatly improved. To use the index for sorting, the following two points must be met.

1. The column order after the ORDER BY clause must be consistent with the column order of the combined index, and the sorting direction (forward/reverse order) of all sorting columns must be consistent;

2. The queried field value needs to be included in the index column and satisfy the covering index.

Recommended courses: MySQL Tutorial.

Let’s analyze it through examples

Create a combined index on the user_test table

ALTER TABLE user_test ADD INDEX index_user(user_name , city , age);

Case where index sorting can be used

SELECT user_name, city, age FROM user_test ORDER BY user_name;
SELECT user_name, city, age FROM user_test ORDER BY user_name, city;
SELECT user_name, city, age FROM user_test ORDER BY user_name DESC, city DESC;
SELECT user_name, city, age FROM user_test WHERE user_name = 'feinik' ORDER BY city;

Note: The fourth sql statement is a bit special, if the where query condition is the first column of the index column, and it is a constant condition , then the index can also be used.

Case in which index sorting cannot be used

sex is not in the index column

SELECT user_name, city, age FROM user_test ORDER BY user_name, sex;

The direction of the sorting column is inconsistent

SELECT user_name, city, age FROM user_test ORDER BY user_name ASC, city DESC;

The desired query The field column sex is not included in the index column

SELECT user_name, city, age, sex FROM user_test ORDER BY user_name;

where query condition user_name is a range query, so other columns of the index cannot be used

SELECT user_name, city, age FROM user_test WHERE user_name LIKE 'feinik%' ORDER BY city;

The above is the detailed content of How to use index in mysql?. For more information, please follow other related articles on the PHP Chinese website!