129 lines
2.6 KiB
Plaintext
129 lines
2.6 KiB
Plaintext
create table t0 (a int);
|
|
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
create table t1 (
|
|
pk int primary key,
|
|
a int not null,
|
|
b int not null,
|
|
key(a) comment 'rev:foo',
|
|
key(b) comment 'bar'
|
|
) engine=xengine;
|
|
insert into t1 select a,a,a from t0;
|
|
insert into t1 select a+10,a+10,a+10 from t0;
|
|
# Primary key is not in a reverse-ordered CF, so full table scan
|
|
# returns rows in ascending order:
|
|
select * from t1;
|
|
pk a b
|
|
0 0 0
|
|
1 1 1
|
|
2 2 2
|
|
3 3 3
|
|
4 4 4
|
|
5 5 5
|
|
6 6 6
|
|
7 7 7
|
|
8 8 8
|
|
9 9 9
|
|
10 10 10
|
|
11 11 11
|
|
12 12 12
|
|
13 13 13
|
|
14 14 14
|
|
15 15 15
|
|
16 16 16
|
|
17 17 17
|
|
18 18 18
|
|
19 19 19
|
|
explain
|
|
select a from t1 order by a limit 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index NULL a 4 NULL # 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` limit 5
|
|
select a from t1 order by a limit 5;
|
|
a
|
|
0
|
|
1
|
|
2
|
|
3
|
|
4
|
|
explain
|
|
select b from t1 order by b limit 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b` limit 5
|
|
select a from t1 order by a limit 5;
|
|
a
|
|
0
|
|
1
|
|
2
|
|
3
|
|
4
|
|
explain
|
|
select a from t1 order by a desc limit 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index NULL a 4 NULL # 100.00 Backward index scan; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 5
|
|
select a from t1 order by a desc limit 5;
|
|
a
|
|
19
|
|
18
|
|
17
|
|
16
|
|
15
|
|
explain
|
|
select b from t1 order by b desc limit 5;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t1 NULL index NULL b 4 NULL # 100.00 Backward index scan; Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`b` desc limit 5
|
|
select b from t1 order by b desc limit 5;
|
|
b
|
|
19
|
|
18
|
|
17
|
|
16
|
|
15
|
|
drop table t1;
|
|
#
|
|
# Try a primary key in a reverse-ordered CF.
|
|
#
|
|
create table t2 (
|
|
pk int,
|
|
a int not null,
|
|
primary key(pk) comment 'rev:cf1'
|
|
) engine=xengine;
|
|
insert into t2 select a,a from t0;
|
|
# Primary key is in a reverse-ordered CF, so full table scan
|
|
# returns rows in descending order:
|
|
select * from t2;
|
|
pk a
|
|
0 0
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
5 5
|
|
6 6
|
|
7 7
|
|
8 8
|
|
9 9
|
|
set autocommit=0;
|
|
begin;
|
|
delete from t2 where a=3 or a=7;
|
|
select * from t2;
|
|
pk a
|
|
0 0
|
|
1 1
|
|
2 2
|
|
4 4
|
|
5 5
|
|
6 6
|
|
8 8
|
|
9 9
|
|
rollback;
|
|
set autocommit=1;
|
|
drop table t2;
|
|
drop table t0;
|