1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
create table t1 (a int primary key, b int, c int, unique abcindex(a,b,c), unique bcindex(b,c));
insert into t1 values (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,2,3),(7,2,4),(8,2,1);
explain select * from t1 force index (abcindex) where a=2 and b=2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ref abcindex abcindex 9 const,const # Using where; Using index
select * from t1 force index (abcindex) where a=2 and b=2;
a b c
2 2 2
explain select a from t1 force index (bcindex) where b=2 and c>2;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 range bcindex bcindex 10 NULL # Using where
select a from t1 force index (bcindex) where b=2 and c>2;
a
6
7
DROP TABLE t1;
|