Analysis of the reasons why the index does not take effect when searching in the MySql range

1 Problem Description

This article sorts the established composite index and takes the non-index fields in the record. It is found that the index is not effective. For example, there is the following table, and the DDL statement is:

CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date NOT NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` enum('M','F') NOT NULL,
 `hire_date` date NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`emp_no`),
 KEY `unique_birth_name` (`first_name`,`last_name`) USING BTREE
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;

The composite index is unique_birth_name (first_name,last_name) . Use the following statement:

EXPLAIN SELECT
 gender
FROM
 employees
ORDER BY
 first_name,
 last_name 

According to the above figure: type:all and Extra:Using filesort, the index is not effective.

Continue the experiment and further rewrite the query statement to add a range search:

EXPLAIN SELECT
 gender
FROM
 employees
WHERE first_name > 'Leah'
ORDER BY
 first_name,
 last_name

The execution plan is shown in the following figure:

The results here are no different from the first SQL analysis. Keep experimenting.

Rewrite the sql statement:

EXPLAIN SELECT
 gender
FROM
 employees
WHERE first_name > 'Tzvetan'
ORDER BY
 first_name,
 last_name 

At this point, surprisingly, the index works.

2 Problem Analysis

At this point, we make a bold guess:

When performing SQL analysis for the first time, because after the first order by, the data of the entire table is still obtained. If each gender is searched and spliced ​​according to the primary key carried in the composite index, it will naturally be very resource-consuming and time-consuming. MySQL will not do such a stupid thing. It is better to directly scan the entire table, and concatenate each scanned data with the temporary data obtained by order by to obtain the required data.

In order to verify the correctness of the above idea, we analyze three SQL statements.

The amount of data obtained by the first SQL based on the composite index is: 300024 , which is the data of the entire table

SELECT
 COUNT(first_name)
FROM
 employees
ORDER BY
 first_name,
 last_name 

The amount of data obtained by the second rewritten SQL based on the composite index is: 159149 , which is 1/2 of the total table data volume.

SELECT
 COUNT(first_name)
FROM
 employees
WHERE first_name > 'Leah'
ORDER BY
 first_name,
 last_name 

The amount of data obtained by the third rewritten SQL based on the composite index is: 36731 , which is 1/10 of the total table data volume.

SELECT
  COUNT(first_name)
FROM
  employees
WHERE first_name > 'Tzvetan'
ORDER BY
  first_name,
  last_name 

By comparison, it was found that the amount of data obtained by the second rewritten SQL based on the composite index was 1/2 of the total table data amount. At this point, MySQL has not yet reached the level of using indexes for secondary searches. The amount of data obtained by the third rewritten SQL based on the composite index is 1/10 of the total table data volume, reaching the level of MySQL using the index for secondary search. Therefore, it can be seen from the execution plan that the third rewritten SQL used the index.

3 Conclusion

Whether MySQL performs a secondary search based on the primary key queried from the first index condition also depends on the amount of data queried. If the amount of data is close to the amount of data in the entire table, a full table scan will be performed. Otherwise, a secondary search will be performed based on the primary key queried from the first time.

This concludes this article on the analysis of the causes of the problem of index not taking effect during MySql range search. For more relevant content on the problem of index not taking effect during MySql range search, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!