How to make mysql-slow.log not telling me that I am not using index?

Question

The table named markets having such columns: id, visible, position, and I created index on visible, every time

and the slow-log always

# Time: 2021-05-12T00:46:14.248654Z
# User@Host: tase[tase] @ ip-172-31-22-13.ap-northeast-1.compute.internal [172.31.22.13]  Id: 134987
# Query_time: 0.000155  Lock_time: 0.000048 Rows_sent: 7  Rows_examined: 17
SET timestamp=1620780374;
SELECT `markets`.* FROM `markets` WHERE `markets`.`visible` = TRUE ORDER BY `markets`.`position` DESC;

and the Explain:

+----+-------------+---------+------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | markets | NULL       | ALL  | index_markets_on_visible| NULL | NULL    | NULL |   10 |    70.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------+---------+------+------+----------+-----------------------------+

1 row in set, 1 warning (0.00 sec)

I am wondering:

1. I have created the index: index_markets_on_visible, why still the query was logged and considered as: "not indexed query"

2. How to solve this issue? should I add index to the "order by" column?

thanks.

Answer 1

SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

It will probably say ON. Turn it OFF. (And you have found why I think that setting is mostly useless.)

If you want to have a useful index, make the composite index INDEX(visible, position).

It may still say that it is not using any index. Here's the reason for that. 7/17 of the rows match visible = TRUE. That is more than (about) 20% of the table, so the Optimizer decides that it is not worth using the index, but it may be faster to simply scan all 17 rows, skipping the non-visible ones.

Another note: If it does use my index, it will avoid the "using filesort", which gives another boost in performance.