Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Not a Bug
-
12.1
-
None
-
Not for Release Notes
Description
In the table there are two same indexes, and by default both of them are used. But if we used hint INDEX() for the one and force index() for the second, the second index is ignored.
Testcase:
--source include/have_sequence.inc
|
create table t1 ( |
a int, |
b int, |
key (b), |
key (a) |
);
|
insert into t1 select seq, seq from seq_1_to_1000; |
|
|
explain select * from t1 where a in (2, 4, 6) and b in (2, 3, 6); |
|
|
explain select /*+ INDEX(t1 a)*/ * from t1 force index(b) where a in (2, 4, 6) and b in (2, 3, 6); |
|
|
explain select /*+ NO_INDEX(t1 a)*/ * from t1 force index(a) where a in (2, 4, 6) and b in (2, 3, 6); |
|
|
drop table t1; |
Actual result:
explain select * from t1 where a in (2, 4, 6) and b in (2, 3, 6); |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t1 range|filter b,a b|a 5|5 NULL 3 (0%) Using index condition; Using where; Using rowid filter |
|
|
explain select /*+ INDEX(t1 a)*/ * from t1 force index(b) where a in (2, 4, 6) and b in (2, 3, 6); |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t1 range a a 5 NULL 3 Using index condition; Using where |
|
|
explain select /*+ NO_INDEX(t1 a)*/ * from t1 force index(a) where a in (2, 4, 6) and b in (2, 3, 6); |
id select_type table type possible_keys key key_len ref rows Extra |
1 SIMPLE t1 range b b 5 NULL 3 Using index condition; Using where |
Attachments
Issue Links
- is caused by
-
MDEV-35856 MySQL 8-style optimizer hints: milestone 2: index hints
-
- Closed
-