MySQL index hint processing when index does not exist

Source: Internet
Author: User
Tags mysql query mysql index
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’

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.