forked from pingcap/tidb
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathclustered_index.test
58 lines (47 loc) · 5.45 KB
/
clustered_index.test
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
48
49
50
51
52
53
54
55
56
57
58
set tidb_cost_model_version=1;
set @@tidb_enable_outer_join_reorder=true;
drop database if exists with_cluster_index;
create database with_cluster_index;
drop database if exists wout_cluster_index;
create database wout_cluster_index;
use with_cluster_index;
create table tbl_0 ( col_0 decimal not null , col_1 blob(207) , col_2 text , col_3 datetime default '1986-07-01' , col_4 bigint unsigned default 1504335725690712365 , primary key idx_0 ( col_3,col_2(1),col_1(6) ) clustered, key idx_1 ( col_3 ), unique key idx_2 ( col_3 ) , unique key idx_3 ( col_0 ) , key idx_4 ( col_1(1),col_2(1) ) , key idx_5 ( col_2(1) ) ) ;
create table tbl_1 ( col_5 char(135) , col_6 bit(17) default 50609 not null , col_7 char(202) default 'IoQWYoGdbbgBDlxpDHQ' , col_8 char(213) , col_9 time not null , primary key idx_6 ( col_6 ) clustered, unique key idx_7 ( col_5 ) ) ;
create table tbl_2 ( col_10 datetime default '1976-05-11' , col_11 datetime , col_12 float , col_13 double(56,29) default 18.0118 , col_14 char not null , primary key idx_8 ( col_14,col_13,col_10 ) clustered, key idx_9 ( col_11 ) ) ;
create table tbl_3 ( col_15 tinyint default -91 not null , col_16 bit(61) default 990141831018971350 not null , col_17 double(244,22) default 3985 not null , col_18 binary(32) default 'kxMlWqvpxXNBlxoU' , col_19 text(401) , primary key idx_10 ( col_18,col_19(4) ) clustered, key idx_11 ( col_17,col_18,col_19(2),col_15,col_16 ) , unique key idx_12 ( col_17 ) ) ;
create table tbl_4 ( col_20 double(230,16) default 8.49 not null , col_21 int unsigned not null , col_22 enum('Alice','Bob','Charlie','David') not null , col_23 float default 3066.13040283622 , col_24 datetime default '1980-10-27' not null , primary key idx_13 ( col_22,col_24 ) clustered, key idx_14 ( col_23,col_20 ) , key idx_15 ( col_24 ) , key idx_16 ( col_20 ) , unique key idx_17 ( col_24 ) , key idx_18 ( col_21 ) ) ;
load stats 's/with_cluster_index_tbl_0.json';
load stats 's/with_cluster_index_tbl_1.json';
load stats 's/with_cluster_index_tbl_2.json';
load stats 's/with_cluster_index_tbl_3.json';
load stats 's/with_cluster_index_tbl_4.json';
use wout_cluster_index;
create table tbl_0 ( col_0 decimal not null , col_1 blob(207) , col_2 text , col_3 datetime default '1986-07-01' , col_4 bigint unsigned default 1504335725690712365 , primary key idx_0 ( col_3,col_2(1),col_1(6) ) nonclustered, key idx_1 ( col_3 ) , unique key idx_2 ( col_3 ) , unique key idx_3 ( col_0 ) , key idx_4 ( col_1(1),col_2(1) ) , key idx_5 ( col_2(1) ) ) ;
create table tbl_1 ( col_5 char(135) , col_6 bit(17) default 50609 not null , col_7 char(202) default 'IoQWYoGdbbgBDlxpDHQ' , col_8 char(213) , col_9 time not null , primary key idx_6 ( col_6 ) nonclustered, unique key idx_7 ( col_5 ) ) ;
create table tbl_2 ( col_10 datetime default '1976-05-11' , col_11 datetime , col_12 float , col_13 double(56,29) default 18.0118 , col_14 char not null , primary key idx_8 ( col_14,col_13,col_10 ) nonclustered, key idx_9 ( col_11 ) ) ;
create table tbl_3 ( col_15 tinyint default -91 not null , col_16 bit(61) default 990141831018971350 not null , col_17 double(244,22) default 3985 not null , col_18 binary(32) default 'kxMlWqvpxXNBlxoU' , col_19 text(401) , primary key idx_10 ( col_18,col_19(4) ) nonclustered, key idx_11 ( col_17,col_18,col_19(2),col_15,col_16 ) , unique key idx_12 ( col_17 ) ) ;
create table tbl_4 ( col_20 double(230,16) default 8.49 not null , col_21 int unsigned not null , col_22 enum('Alice','Bob','Charlie','David') not null , col_23 float default 3066.13040283622 , col_24 datetime default '1980-10-27' not null , primary key idx_13 ( col_22,col_24 ) nonclustered, key idx_14 ( col_23,col_20 ) , key idx_15 ( col_24 ) , key idx_16 ( col_20 ) , unique key idx_17 ( col_24 ) , key idx_18 ( col_21 ) ) ;
load stats 's/wout_cluster_index_tbl_0.json';
load stats 's/wout_cluster_index_tbl_1.json';
load stats 's/wout_cluster_index_tbl_2.json';
load stats 's/wout_cluster_index_tbl_3.json';
load stats 's/wout_cluster_index_tbl_4.json';
explain select count(*) from with_cluster_index.tbl_0 where col_0 < 5429 ;
explain select count(*) from wout_cluster_index.tbl_0 where col_0 < 5429 ;
explain select count(*) from with_cluster_index.tbl_0 where col_0 < 41 ;
explain select count(*) from wout_cluster_index.tbl_0 where col_0 < 41 ;
explain select col_14 from with_cluster_index.tbl_2 where col_11 <> '2013-11-01' ;
explain select col_14 from wout_cluster_index.tbl_2 where col_11 <> '2013-11-01' ;
explain select sum( col_4 ) from with_cluster_index.tbl_0 where col_3 != '1993-12-02' ;
explain select sum( col_4 ) from wout_cluster_index.tbl_0 where col_3 != '1993-12-02' ;
explain select col_0 from with_cluster_index.tbl_0 where col_0 <= 0 ;
explain select col_0 from wout_cluster_index.tbl_0 where col_0 <= 0 ;
explain select col_3 from with_cluster_index.tbl_0 where col_3 >= '1981-09-15' ;
explain select col_3 from wout_cluster_index.tbl_0 where col_3 >= '1981-09-15' ;
explain select tbl_2.col_14 , tbl_0.col_1 from with_cluster_index.tbl_2 right join with_cluster_index.tbl_0 on col_3 = col_11 ;
explain select tbl_2.col_14 , tbl_0.col_1 from wout_cluster_index.tbl_2 right join wout_cluster_index.tbl_0 on col_3 = col_11 ;
explain select count(*) from with_cluster_index.tbl_0 where col_0 <= 0 ;
explain select count(*) from wout_cluster_index.tbl_0 where col_0 <= 0 ;
explain select count(*) from with_cluster_index.tbl_0 where col_0 >= 803163 ;
explain select count(*) from wout_cluster_index.tbl_0 where col_0 >= 803163 ;
set @@tidb_enable_outer_join_reorder=false;