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

Does mysql use indexes? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

In the sorting operation, if you can use the index to sort, then you can greatly improve the speed of sorting, to use the index to sort need to meet the following two points.

1. The column order after the ORDER BY clause should be the same as that of the composite index, and the sorting direction of all sort sequences (positive / reverse) should be the same.

2. The field values queried need to be included in the index column and satisfy the overlay index.

Recommended course: MySQL tutorial.

Analyze concretely through examples

Create a composite index on the user_ test table

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

Cases in which 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 is a constant condition, then the index can also be used.

Cases 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 arrangement is not consistent.

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

The field column sex to be queried is not included in the index column

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

The user_name after the where query condition 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; Thank you for your reading! After reading the above, do you have a general understanding of the index used by mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.