MySQL index hint processing when index does not exist

About index_hint
   In the mysql query statement, you can specify the index_hint to tell the optimizer how to use the index. For details, refer to here.

index_hint:
    USE {INDEX | KEY}
      [FOR {JOIN | ORDER BY | GROUP BY}] ([index_list])
  | IGNORE {INDEX | KEY}
      [FOR {JOIN | ORDER BY | GROUP BY}] (index_list)
  | FORCE {INDEX | KEY}
      [FOR {JOIN | ORDER BY | GROUP BY}] (index_list)
 

problem
  When index_hint is used in a query, if the index specified by index_hint does not exist, the server returns error ERROR 1176 (42000). See the following example

drop table t1;
create table t1 (id int auto_increment, a char (2), primary key (id), key idx1 (a)) engine = innodb;
insert into t1 values (1, ‘ab’);
select * from t1 force index (idx1) where a = ‘ab’;
+ ---- + ------ +
| id | a |
+ ---- + ------ +
| 1 | ab |
+ ---- + ------ +
1 row in set (0.00 sec)
alter table t1 drop key idx1;
select * from t1 force index (idx1) where a = ‘ab’;
ERROR 1176 (42000): Key ‘idx1’ does n’t exist in table ‘t1’
    In practical applications, if the query statement uses index_hint to specify an index, this index can become redundant with the development of the business, but deleting this index will cause an application error. So either keep a useless index or modify SQL to republish the application. Neither approach is very friendly.

Improve
  If the index specified by index_hint does not exist, the server does not return an error, and instead chooses to report warining, then deleting this index will not cause an error to the application.

  Improve:

    Added sql_mode type INDEX_HINE_ERROR;

    When sql_mode is set to INDEX_HINE_ERROR, if the index specified by index_hint does not exist, the server returns an error.

    When sql_mode does not set the INDEX_HINE_ERROR type, if the index specified by index_hint does not exist, the server does not return an error, but instead reports warining

  Look at the following example:

 1) sql_mode is not set INDEX_HINE_ERROR type

set sql_mode = ‘‘;
select * from t1 force index (idx1) where a = ‘ab’;
+ ---- + ------ +
| id | a |
+ ---- + ------ +
| 1 | ab |
+ ---- + ------ +
1 row in set, 1 warning (0.00 sec)
show warnings;
+ --------- + ------ + -------------------------------- -------- +
| Level | Code | Message |
+ --------- + ------ + -------------------------------- -------- +
Warning | 1176 | Key ‘idx1’ does n’t exist in table ‘t1‘ |
+ --------- + ------ + -------------------------------- -------- +
1 row in set (0.00 sec)
explain select * from t1 force index (idx1) where a = ‘ab’;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------ + ------------- +
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------ + ------------- +
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------ + ------------- +
1 row in set, 1 warning (0.00 sec)
show warnings;
+ --------- + ------ + -------------------------------- -------- +
| Level | Code | Message |
+ --------- + ------ + -------------------------------- -------- +
Warning | 1176 | Key ‘idx1’ does n’t exist in table ‘t1‘ |
+ --------- + ------ + -------------------------------- -------- +
1 row in set (0.00 sec)
 

 2) sql_mode is set to INDEX_HINE_ERROR

set sql_mode = ‘index_hint_error’;
select * from t1 force index (idx1) where a = ‘ab’;
ERROR 1176 (42000): Key ‘idx1’ does n’t exist in table ‘t1’
explain select * from t1 force index (idx1) where a = ‘ab’;
ERROR 1176 (42000): Key ‘idx1’ does n’t exist in table ‘t1’