Why does MySQL not always use index for select query?

Question

I have two tables in my database users and articles.

Records in my users and articles table are given below:

+----+--------+
| id | name   |
+----+--------+
|  1 | user1  |
|  2 | user2  |
|  3 | user3  |
+----+--------+


+----+---------+----------+
| id | user_id | article  |
+----+---------+----------+
|  1 |       1 | article1 |
|  2 |       1 | article2 |
|  3 |       1 | article3 |
|  4 |       2 | article4 |
|  5 |       2 | article5 |
|  6 |       3 | article6 |
+----+---------+----------+

Given below the queries and the respected EXPLAIN output.

EXPLAIN SELECT * FROM articles WHERE user_id = 1;

+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | articles | NULL       | ALL  | user_id       | NULL | NULL    | NULL |    6 |    50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+



EXPLAIN SELECT * FROM articles WHERE user_id = 2;
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | articles | NULL       | ref  | user_id       | user_id | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+


EXPLAIN SELECT * FROM articles WHERE user_id = 3;
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | articles | NULL       | ref  | user_id       | user_id | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+

Looking at the EXPLAIN plans for my select queries, it seems that queries are not always using the indexes.

In case,

when user_id is 1, it doesn't use the key and scans the complete table.

otherwise, it uses the user_id key and scans only few rows.

Could you please explain why queries don't always use the index here?

That is mysql optimizer territory. dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html In some cases, a query can be optimized to retrieve values without consulting the data rows. and at the bottom of that article Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. — Alex
– Alex, Commented May 27, 2020 at 14:50
Your tables have so few rows, that using an index may end up being more expensive than just reading the whole thing at once. Indexes are quite performant when you retrieve 1) a few rows, 2) from a large table; the second condition is not met in your example. — The Impaler
– The Impaler, Commented May 27, 2020 at 14:54
Thank you for making this clear. I got the point. But I am still confused about the inconsistent behaviour of the queries. Using Index may end up being more expensive then why queries using the index when user_id is 2 or 3? — imvishi
– imvishi, Commented May 27, 2020 at 16:40
Because it depends on the selectivity of the value - if user_id of 1 is much more common than the values 2 or 3 the optimizer might decide that value of 1 has lower selectivity and so it would be better to do a full table scan. This behavior is controlled by the statistics that is collected during INSERT/UPDATE/DELETE. Take a look at these articles dev.mysql.com/doc/refman/8.0/en/statistics-table.html percona.com/blog/2017/09/11/… — IVO GELOV
– IVO GELOV, Commented May 27, 2020 at 17:24

Rick James · Accepted Answer · 2020-05-30 02:23:40Z

There are (probably) two BTrees involved in the queries you show. One BTree for the data, sorted by the PRIMARY KEY, which I assume is id. The other for the INDEX on user_id (again, I am guessing). When InnoDB (which I assume you are using) builds a "secondary index", such as INDEX(user_id), it silently tacks on the PK of the table. So, effectively it becomes a BTree containing two columns: (user_id, id) and sorted by that pair.

When the Optimizer looks at SELECT * FROM t WHERE user_id=?, it probed the table and discovered that "a lot" of rows had user_id = 1 and not many rows had the other values you tried.

The Optimizer has two (or more) ways to evaluate the queries like that --

Plan A (use the index): Here's what it does:

Drill down the Index's BTree to find the first occurrence of user_id=2.
There it will find an id.
Use that id to drill down the data's BTree to find * (as in SELECT *).
Move on to the next entry in the Index BTree. (This is actually rather efficient since it is really a "B+Tree"; see Wikipedia.)
If found, loop back to step 2. If not found (no more index entries with user_id=2), exit.

Plan B (don't use the index -- useful for your user_id=1):

Simply walk through the data BTree in whatever order.
Skip any row that does not have user_id=1.

The bouncing back and forth between the two BTrees costs something. The Optimizer decided your =1 case would need to look at more than about 20% of the table and decided that plan B would be faster. That is, it deliberately ignored the INDEX.

There are a lot of factors that the Optimizer can't or doesn't estimate correctly, but generally picking between these two Plans leads to faster execution. (Your table is too small to reliably measure a difference.)

Other "Plans" -- If the index is "covering", there is no need to use the data BTree. If there is an ORDER BY that can be used, then the Optimizer will probably use Plan A to avoid the "filesort". (See EXPLAIN SELECT ...) Etc.

Collectives™ on Stack Overflow

Why does MySQL not always use index for select query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related