github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/ddl/secondary_index_create.result (about) 1 drop table if exists t1; 2 create table t1(id int PRIMARY KEY,name VARCHAR(255),age int); 3 create index idx1 on t1(name); 4 insert into t1 values(1,"Abby", 24); 5 insert into t1 values(2,"Bob", 25); 6 select * from t1; 7 id name age 8 1 Abby 24 9 2 Bob 25 10 show index from t1; 11 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 12 t1 0 PRIMARY 1 id A 0 NULL NULL YES NULL 13 t1 1 idx1 1 name A 0 NULL NULL YES YES NULL 14 show create table t1; 15 Table Create Table 16 t1 CREATE TABLE `t1` (\n`id` INT NOT NULL,\n`name` VARCHAR(255) DEFAULT NULL,\n`age` INT DEFAULT NULL,\nPRIMARY KEY (`id`),\nKEY `idx1` (`name`)\n) 17 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1"; 18 name type column_name 19 idx1 MULTIPLE name 20 idx1 MULTIPLE __mo_alias_id 21 drop table if exists t1; 22 create table t1(id int PRIMARY KEY,name VARCHAR(255),age int); 23 insert into t1 values(1,"Abby", 24); 24 insert into t1 values(2,"Bob", 25); 25 create index idx2 on t1(name); 26 insert into t1 values(3,"Dora", 30); 27 select * from t1; 28 id name age 29 1 Abby 24 30 2 Bob 25 31 3 Dora 30 32 show index from t1; 33 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 34 t1 0 PRIMARY 1 id A 0 NULL NULL YES NULL 35 t1 1 idx2 1 name A 0 NULL NULL YES YES NULL 36 show create table t1; 37 Table Create Table 38 t1 CREATE TABLE `t1` (\n`id` INT NOT NULL,\n`name` VARCHAR(255) DEFAULT NULL,\n`age` INT DEFAULT NULL,\nPRIMARY KEY (`id`),\nKEY `idx2` (`name`)\n) 39 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx2"; 40 name type column_name 41 idx2 MULTIPLE name 42 idx2 MULTIPLE __mo_alias_id 43 drop table if exists t1; 44 create table t1(id int PRIMARY KEY,name VARCHAR(255),age int); 45 create index idx3 on t1(id,name); 46 insert into t1 values(1,"Abby", 24); 47 insert into t1 values(2,"Bob", 25); 48 select * from t1; 49 id name age 50 1 Abby 24 51 2 Bob 25 52 show index from t1; 53 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 54 t1 0 PRIMARY 1 id A 0 NULL NULL YES NULL 55 t1 1 idx3 1 id A 0 NULL NULL YES NULL 56 t1 1 idx3 2 name A 0 NULL NULL YES YES NULL 57 show create table t1; 58 Table Create Table 59 t1 CREATE TABLE `t1` (\n`id` INT NOT NULL,\n`name` VARCHAR(255) DEFAULT NULL,\n`age` INT DEFAULT NULL,\nPRIMARY KEY (`id`),\nKEY `idx3` (`id`,`name`)\n) 60 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx3"; 61 name type column_name 62 idx3 MULTIPLE id 63 idx3 MULTIPLE name 64 drop table if exists t1; 65 create table t1(id VARCHAR(255) PRIMARY KEY,name VARCHAR(255),age int); 66 insert into t1 values("a","Abby", 24); 67 insert into t1 values("b","Bob", 25); 68 alter table t1 add key idx4 (name); 69 insert into t1 values("c","Danny", 26); 70 select * from t1; 71 id name age 72 a Abby 24 73 b Bob 25 74 c Danny 26 75 show index from t1; 76 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 77 t1 0 PRIMARY 1 id A 0 NULL NULL YES NULL 78 t1 1 idx4 1 name A 0 NULL NULL YES YES NULL 79 show create table t1; 80 Table Create Table 81 t1 CREATE TABLE `t1` (\n`id` VARCHAR(255) NOT NULL,\n`name` VARCHAR(255) DEFAULT NULL,\n`age` INT DEFAULT NULL,\nPRIMARY KEY (`id`),\nKEY `idx4` (`name`)\n) 82 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx4"; 83 name type column_name 84 idx4 MULTIPLE name 85 idx4 MULTIPLE __mo_alias_id 86 drop table if exists t1; 87 create table t1(id VARCHAR(255) PRIMARY KEY,name VARCHAR(255),age int, index(name)); 88 insert into t1 values("a","Abby", 24); 89 insert into t1 values("b","Bob", 25); 90 insert into t1 values("c","Carol", 23); 91 select * from t1; 92 id name age 93 a Abby 24 94 b Bob 25 95 c Carol 23 96 show index from t1; 97 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 98 t1 1 name 1 name A 0 NULL NULL YES YES NULL 99 t1 0 PRIMARY 1 id A 0 NULL NULL YES NULL 100 show create table t1; 101 Table Create Table 102 t1 CREATE TABLE `t1` (\n`id` VARCHAR(255) NOT NULL,\n`name` VARCHAR(255) DEFAULT NULL,\n`age` INT DEFAULT NULL,\nPRIMARY KEY (`id`),\nKEY `name` (`name`)\n) 103 select name, type, column_name from mo_catalog.mo_indexes mi where mi.column_name ="name" or mi.column_name="__mo_alias_id"; 104 name type column_name 105 name MULTIPLE name 106 name MULTIPLE __mo_alias_id 107 PRIMARY PRIMARY name 108 PRIMARY PRIMARY name 109 name UNIQUE name 110 name UNIQUE name 111 drop table if exists t1; 112 create table t1(a double primary key, b int); 113 insert into t1 values(1.5,100); 114 create index idx6 on t1(a); -- sk = pk 115 insert into t1 values(2.6,200); 116 select * from t1; 117 a b 118 1.5 100 119 2.6 200 120 show index from t1; 121 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 122 t1 0 PRIMARY 1 a A 0 NULL NULL YES NULL 123 t1 1 idx6 1 a A 0 NULL NULL YES NULL 124 show create table t1; 125 Table Create Table 126 t1 CREATE TABLE `t1` (\n`a` DOUBLE NOT NULL,\n`b` INT DEFAULT NULL,\nPRIMARY KEY (`a`),\nKEY `idx6` (`a`)\n) 127 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx6"; 128 name type column_name 129 idx6 MULTIPLE a 130 drop table if exists t1; 131 create table t1(id int PRIMARY KEY,name VARCHAR(255),age int); 132 create index idx7 using BTREE on t1(name); 133 insert into t1 values(1,"Abby", 24); 134 show index from t1; 135 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 136 t1 0 PRIMARY 1 id A 0 NULL NULL YES NULL 137 t1 1 idx7 1 name A 0 NULL NULL YES btree YES NULL 138 show create table t1; 139 Table Create Table 140 t1 CREATE TABLE `t1` (\n`id` INT NOT NULL,\n`name` VARCHAR(255) DEFAULT NULL,\n`age` INT DEFAULT NULL,\nPRIMARY KEY (`id`),\nKEY `idx7` USING btree (`name`)\n) 141 select name, type, column_name from mo_catalog.mo_indexes mi where name="idx7"; 142 name type column_name 143 idx7 MULTIPLE name 144 idx7 MULTIPLE __mo_alias_id