github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/secondary_index_create.sql (about)

     1  -- 1.c Create Secondary Index before table population
     2  drop table if exists t1;
     3  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
     4  create index idx1 on t1(name);
     5  insert into t1 values(1,"Abby", 24);
     6  insert into t1 values(2,"Bob", 25);
     7  select * from t1;
     8  show index from t1;
     9  show create table t1;
    10  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1";
    11  -- select a,b, a=b, id from (select serial_full(name,id) as a,id from t1) as v1 inner join (select __mo_index_idx_col as b, __mo_index_pri_col from `__mo_index_secondary_d4ea54e0-6c04-11ee-a57e-723e89f7b972`) as v2 on v1.id = v2.__mo_index_pri_col;
    12  
    13  
    14  -- 1.a Create Secondary Index after table population [Without User defined PK in SK column list]
    15  drop table if exists t1;
    16  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    17  insert into t1 values(1,"Abby", 24);
    18  insert into t1 values(2,"Bob", 25);
    19  create index idx2 on t1(name);
    20  insert into t1 values(3,"Dora", 30);
    21  select * from t1;
    22  show index from t1;
    23  show create table t1;
    24  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx2";
    25  
    26  
    27  -- 1.c Create Secondary Index containing the PK column [With user defined PK in SK column list]
    28  drop table if exists t1;
    29  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    30  create index idx3 on t1(id,name);
    31  insert into t1 values(1,"Abby", 24);
    32  insert into t1 values(2,"Bob", 25);
    33  select * from t1;
    34  show index from t1;
    35  show create table t1;
    36  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx3";
    37  
    38  
    39  -- 1.d Create Secondary Index by using "alter table t1 add key/index"
    40  drop table if exists t1;
    41  create table t1(id VARCHAR(255) PRIMARY KEY,name VARCHAR(255),age int);
    42  insert into t1 values("a","Abby", 24);
    43  insert into t1 values("b","Bob", 25);
    44  alter table t1 add key idx4 (name);
    45  insert into t1 values("c","Danny", 26);
    46  select * from t1;
    47  show index from t1;
    48  show create table t1;
    49  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx4";
    50  
    51  
    52  -- 1.e Create Table syntax
    53  drop table if exists t1;
    54  create table t1(id VARCHAR(255) PRIMARY KEY,name VARCHAR(255),age int, index(name));
    55  insert into t1 values("a","Abby", 24);
    56  insert into t1 values("b","Bob", 25);
    57  insert into t1 values("c","Carol", 23);
    58  select * from t1;
    59  show index from t1;
    60  show create table t1;
    61  select name, type, column_name from mo_catalog.mo_indexes mi where mi.column_name ="name" or mi.column_name="__mo_alias_id";
    62  
    63  -- 1.f Create Secondary Index on PK alone. ie SK = PK.
    64  drop table if exists t1;
    65  create table t1(a double primary key, b int);
    66  insert into t1 values(1.5,100);
    67  create index idx6 on t1(a); -- sk = pk
    68  insert into t1 values(2.6,200);
    69  select * from t1;
    70  show index from t1;
    71  show create table t1;
    72  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx6";
    73  
    74  -- 1.g Create Secondary Index with "using BTREE"
    75  drop table if exists t1;
    76  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    77  create index idx7 using BTREE on t1(name);
    78  insert into t1 values(1,"Abby", 24);
    79  show index from t1;
    80  show create table t1;
    81  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx7";