mirror of
https://github.com/MariaDB/server.git
synced 2025-04-21 22:55:35 +02:00
47 lines
2.2 KiB
Text
47 lines
2.2 KiB
Text
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;
|