MySQL Bugs: #114825: Index hints do not work expectedly

Bug #114825	Index hints do not work expectedly
Submitted:	30 Apr 2024 14:33	Modified:	30 Apr 2024 15:10
Reporter:	John Jove	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I use index hint USE INDEX to force a query to execute with the index. However, when getting the query plan, no index is used.

mysql> EXPLAIN SELECT /*+ JOIN_ORDER(t7,t5) */  c2 AS ca1, t7.c1 AS ca2  FROM t5 USE INDEX (i6) NATURAL JOIN t7;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t7    | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t5    | NULL       | ALL  | i6            | NULL | NULL    | NULL |   12 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

How to repeat:
mysql> use test;
Database changed
mysql> source init_database.sql
mysql> EXPLAIN SELECT /*+ JOIN_ORDER(t7,t5) */  c2 AS ca1, t7.c1 AS ca2  FROM t5 USE INDEX (i6) NATURAL JOIN t7;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t7    | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    2 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t5    | NULL       | ALL  | i6            | NULL | NULL    | NULL |   12 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+

init_database.sql

Attachment: init_database.sql (application/octet-stream, text), 8.05 KiB.

Hi Mr. Jove,

Thank you for your bug report.

However, it is not a bug.

USE INDEX is a hint ...... but Optimiser can avoid it if using no index is faster.

FORCE INDEX is what you are looking for, because it will be used even if it is slower:

+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref        | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+
|  1 | SIMPLE      | t7    | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL       |    1 |   100.00 | NULL                  |
|  1 | SIMPLE      | t5    | NULL       | ref  | i6            | i6   | 6       | test.t7.c1 |   30 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+------------+------+----------+-----------------------+

Not a bug.

Thanks, I got it. When applying USE INDEX, the optimizer decides the final choice.
By the way, do optimizer hints perform like a hint or an enforcement?

Mr. Jove,

Optimiser hints are enforced, sometimes even at the detriment to performance.

No need to reply to this message.