github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimizer/index.test (about)

     1  SET GLOBAL experimental_master_index = 1;
     2  drop database if exists d1;
     3  create database d1;
     4  use d1;
     5  drop table if exists t1;
     6  create table t1(c1 int primary key, c2 int, c3 int, key(c2), unique key id1(c3));
     7  insert into t1 select *,*,* from generate_series(1,100000) g;
     8  insert into t1 select *,1111,* from generate_series(100001,150000) g;
     9  drop table if exists t2;
    10  create table t2(c0 int, c1 int , c2 int, c3 int, primary key(c0,c1),key(c2), unique key id1(c3));
    11  insert into t2 select *,*,*,* from generate_series(1,100000) g;
    12  insert into t2 select *,*,1111,* from generate_series(100001,150000) g;
    13  -- @separator:table
    14  select mo_ctl('dn', 'flush', 'd1.t1');
    15  -- @separator:table
    16  select mo_ctl('dn', 'flush', 'd1.t2');
    17  select Sleep(1);
    18  select * from t1 where c2=1;
    19  select count(c3) from t1 where c2=1111;
    20  select * from t1 where c3=1234;
    21  select * from t1 where c3=5678;
    22  select * from t1 where c2 between 1 and 3;
    23  select * from t1 where c3 between 2 and 10;
    24  select count(c3) from t1 where c2 between 1000 and 1100;
    25  select count(c3) from t1 where c2 between 1000 and 1200;
    26  select count(c3) from t1 where c2 between 1000 and 2000;
    27  select count(c3) from t1 where c2 between 1000 and 3000;
    28  select count(c3) from t1 where c3 between 1000 and 1100;
    29  select count(c3) from t1 where c2 between 1000 and 2000;
    30  select * from t1 where c2 in (1);
    31  select * from t1 where c2 in (1,1000,2000);
    32  select count(c3) from t1 where c2 in (1,1000,1111,2000);
    33  select * from t2 where c2=1;
    34  select count(c3) from t2 where c2=1111;
    35  select * from t2 where c3=1234;
    36  select * from t2 where c3=5678;
    37  select * from t2 where c2 between 1 and 3;
    38  select * from t2 where c3 between 2 and 10;
    39  select count(c3) from t2 where c2 between 1000 and 1100;
    40  select count(c3) from t2 where c2 between 1000 and 1200;
    41  select count(c3) from t2 where c2 between 1000 and 2000;
    42  select count(c3) from t2 where c2 between 1000 and 3000;
    43  select count(c3) from t2 where c3 between 1000 and 1100;
    44  select count(c3) from t2 where c2 between 1000 and 2000;
    45  select * from t2 where c2 in (1);
    46  select * from t2 where c2 in (1,1000,2000);
    47  select count(c3) from t2 where c2 in (1,1000,1111,2000);
    48  select c3,c2 from t1 where c2=1111 order by c3 limit 10;
    49  select * from t1 where c2=-1;
    50  insert into t1 values(-1,-1,-1);
    51  select * from t1 where c2=-1;
    52  select * from t1 where c2=-2;
    53  drop table if exists t1;
    54  create table t1(a varchar(30), b varchar(30), c varchar(30));
    55  create index idx1 using master on t1(a,b,c);
    56  insert into t1 values("Congress","Lane", "1");
    57  insert into t1 values("Juniper","Way", "2");
    58  insert into t1 values("Nightingale","Lane", "3");
    59  select * from t1 where a in ("Congress","Nightingale") and b="Lane" and c in("1","2","3");
    60  drop table if exists t1;
    61  create table t1(a int primary key, b int unique key);
    62  insert into t1 select result, result from generate_series(1,10000)g;
    63  -- @separator:table
    64  select mo_ctl('dn','flush','d1.t1');
    65  select * from t1 where b in (1,2) for update;
    66  drop database if exists d1;
    67  SET GLOBAL experimental_master_index = 0;