Why covering index not used in the case of meeting the conditions?

Question

A covering index is a special case of an index in InnoDB where all required fields for a query are included in the index, as mentioned in this blog https://blog.toadworld.com/2017/04/06/speed-up-your-queries-using-the-covering-index-in-mysql.

But, I encountered a situation that the covering index is not used when SELECT and WHERE only includes indexed columns or primary key.

MySQL version: 5.7.27

Example table:

mysql> SHOW CREATE TABLE employees.employees\G;
*************************** 1. row ***************************
       Table: employees
Create Table: 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,
  PRIMARY KEY (`emp_no`),
  KEY `first_name_last_name` (`first_name`,`last_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Rows: 300024

Indexes:

mysql> SHOW INDEX FROM employees.employees;
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees |          0 | PRIMARY              |            1 | emp_no      | A         |      299379 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | first_name_last_name |            1 | first_name  | A         |        1242 |     NULL | NULL   |      | BTREE      |         |               |
| employees |          1 | first_name_last_name |            2 | last_name   | A         |      276690 |     NULL | NULL   |      | BTREE      |         |               |
+-----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> EXPLAIN SELECT first_name, last_name FROM employees.employees WHERE emp_no < '10010';
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    9 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

As can be seen, the first_name and last_name in the SELECT clause are indexed columns, and the emp_no in the WHERE clause is primary key. But, the execution plan shows that the result rows is retrieved from primary index tree.

In my opinion, it should scan the secondary index tree, and filter results by emp_no < '10010', in which the covering index is used.

Edit

Besides, I have seen the covering index is used in the same situation under MySQL 5.7.21.

Indexes:

Rows:8204

SQL:

explain select poi_id , ctime from another_table where id < 1000;

Result:

Hooman Bahreini · Answer 1 · 2019-10-15T01:58:46.340

You have 2 indices, a primary key (clustered index) on emp_no and a secondary (non-clustered) index on first_name_last_name.

This is how these indices look like:

Now when you run the following query:

SELECT first_name, last_name FROM employees.employees WHERE emp_no < '10010';

The SQL optimizer needs to find all the records with emp_ne < 10010. Your first_name_last_name index does not help finding records with emp_no smaller than 10010, because it does not hold this information.

So SQL optimizer would search your clustered index to find all the employees with the required employee number, there is not reason to get the first name and last name from the secondary index because SQL optimizer has already found this information.

Now if you change the query to:

SELECT * FROM employees.employees WHERE first_name = 'john';

Then the SQL optimizer would use your secondary (non-clustered) index to find the records, because it is the easiest way to narrow down the search result.

Note:

If you run the following query:

SELECT * FROM employees.employees WHERE last_name = 'smith';

Your secondary index would not be used, because your secondary index is a composite index containing first_name and last_name... since the index is sorted by first_name then by last_name it won't be useful for a search query on last_name. In this case, SQL optimizer would scan your entire table to find the records with last_name = 'smith'

Update

Think of it as an index at the end of a book. Imagine you have a tourist guide book for Brazil... it has an index of all restaurants and another index of all hotels in Brazil.

Restaurant Index

Restaurant 1: is mentioned on page 12 and 77 of Brazil guide book
Restaurant 2: is mentioned on page 33 of Brazil guide book
...

Hotel Index

Hotel 1: is mentioned on page 5 of Brazil guide book
Hotel 2: is mentioned on page 33 and 39 of Brazil guide book
...

Now if you want to search the book and find all pages that mention the city of Rio De Janeiro, neither of these indices are useful. Unless the book has a third index on city names, you would have to scan the whole book to find those pages.

I have given another example that I encountered. Could you explain why? — 张拉拉, Oct 12 '19 at 07:00
What you need to realize is that an index is only useful when you are searching on it... in your second example, the secondary index does not contain any information about id and you are searching on id... how do you expect MySQL server to use that index to search for id? — Hooman Bahreini, Oct 12 '19 at 07:10
I think the two examples should get the same result, both use covering index or both use primary index. Because in the two examples, the columns in the SELECT clause are secondary index, and the columns in the WHERE clause are primary index. Besides, as you said, in the second example, the secondary index does not include id, so the `type` of execution plan is `index`, which means MySQL scans the secondary index and use `id` for filtering. — 张拉拉, Oct 12 '19 at 07:39
You can see that in both cases the field `possible_keys` contains your PRIMARY index... so it means MySQL optimizer is aware that it can use your Primary Key index for the search... but you cannot force optimizer to use your index... for example, imagine you have only 5 records in your table... SQL optimizer would decide that a table scan is a faster way than going through the index. SQL optimizer use heuristics to decide that best execution plan... — Hooman Bahreini, Oct 12 '19 at 07:52

Why covering index not used in the case of meeting the conditions?

1 Answers1

Update