Why MySQL chooses range scan (slow) vs index scan (faster)

Question

I'm running into an issue where I have a table, described as follows:

mysql> desc myTable;
+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| score               | int(11)      | YES  | MUL | 0       |                |
| created_at          | datetime     | YES  |     | NULL    |                |
| updated_at          | datetime     | YES  |     | NULL    |                |
| previous_score      | int(11)      | NO   | MUL | 0       |                |
+---------------------+--------------+------+-----+---------+----------------+

With the following indexes:

mysql> show indexes from myTable;
+-------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                           | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| myTable |          0 | PRIMARY                          |            1 | id             | A         |          48 |     NULL | NULL   |      | BTREE      |         |               |
| myTable |          1 | index_my_table_on_previous_score |            1 | previous_score | A         |          48 |     NULL | NULL   |      | BTREE      |         |               |
| myTable |          1 | index_my_table_on_score          |            1 | score          | A         |          48 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The issue is the following:

mysql> select count(*) from myTable where score > 10;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.07 sec)

mysql> select count(*) from myTable ignore index(index_my_table_on_score) where score > 10;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

As you can see, using an index produces slower results than doing a full table scan. Since I have another index on the column previous_score, I decided to use explain to try and understand the issue further:

mysql> select count(*) from myTable where previous_score > 10;
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

So, as you can see, nice and fast. Let's do the explain comparison:

mysql> explain select count(*) from myTable where score > 10;
+----+-------------+-------+-------+----------------------+----------------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------------+----------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | myTable | range | index_my_table_on_score | index_my_table_on_score | 5       | NULL |   24 | Using where; Using index |
+----+-------------+-------+-------+----------------------+----------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select count(*) from myTable where previous_score > 10;
+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys                    | key                              | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | myTable | index | index_my_table_on_previous_score | index_my_table_on_previous_score | 4       | NULL |   48 | Using where; Using index |
+----+-------------+-------+-------+-------------------------------+-------------------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

As additional information, the query I'm executing goes through all the results from my table (48).

• Why, if the columns are defined exactly the same, and the indexes are exaclty the same, MySQL chooses to do a range scan for score, and a full index scan for previous_score?
• Secondly, why is a range scan slower than a full index scan? Range scan is supposed to be better than index scan, and index scan better than a full table scan. This is obviously not the case for my particular use case.
• Is there a way to suggest MySQL to do an index scan instead of a range scan?

Any explanation or suggestion is really appreciated.

Answer 1

For tiny tables like this, here is a way that is better than timing:

FLUSH STATUS;
SELECT SQL_NO_CACHE ...;
SHOW SESSION STATUS LIKE 'Handler%';

Summing up the column of numbers is a pretty good metric for comparing one variation of the SELECT to another.

Both of the EXPLAINs say they were Using index -- actually performing the query in the BTree that forms the index. No table scan.

Please provide SHOW CREATE TABLE, it is more descriptive than DESCRIBE.

If the 'cache' was cold, that could explain the 0.07 sec.