1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
drop table if exists t1,t2,t3;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a int);
insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
CREATE TABLE t3 (
col1 int(10) unsigned NOT NULL DEFAULT '0',
col2 mediumint(8) unsigned NOT NULL DEFAULT '0',
col3 smallint(5) NOT NULL DEFAULT '1',
filler varchar(255) DEFAULT NULL,
KEY pk_ersatz(col1,col2,col3),
KEY key1 (col1,col2) USING BTREE
) ENGINE=TokuDB DEFAULT CHARSET=latin1 PACK_KEYS=1;
insert into t3 select 1300000000+a, 12345, 7890, 'data' from t2;
insert into t3 select 1400000000+a, 12345, 7890, 'data' from t2;
insert into t3 select 1410799999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1410899999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1410999999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411099999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411199999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411299999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411399999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411499999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411599999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411699999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411899999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1411999999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1412099999+a, 12345, 7890, 'data' from t2;
insert into t3 select 1412199999+a, 12345, 7890, 'data' from t2;
# The following must use range(PRIMARY):
explain
select col1,col2,col3
from t3
where col1 <= 1410799999
order by col1 desc,col2 desc,col3 desc limit 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 range pk_ersatz,key1 pk_ersatz 4 NULL # Using where; Using index
# The same query but the constant is bigger.
# The query should use range(PRIMARY), not full index scan:
explain
select col1,col2,col3
from t3
where col1 <= 1412199999
order by col1 desc, col2 desc, col3 desc limit 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t3 range pk_ersatz,key1 pk_ersatz 4 NULL # Using where; Using index
drop table t1,t2,t3;
|
