In MySQL mode of OceanBase Database, you can create an index, add an index, drop an index, and rename an index.
Create indexes
Syntax:
CREATE INDEX index_name ON table_name (index_col_name,...);
When you create an index on a table, this table still supports read and write operations.
Sample code:
obclient> CREATE TABLE tbl1 (c1 INT PRIMARY KEY, c2 VARCHAR(10));
Query OK, 0 rows affected
obclient> CREATE INDEX tbl1_idx ON tbl1 (c1, c2 ASC);
Query OK, 0 rows affected
ADD INDEX
Syntax:
ALTER TABLE table_name ADD INDEX index_name (index_col_name,...);
Sample code:
obclient> CREATE TABLE tbl2(c1 INT PRIMARY KEY,c2 INT);
Query OK, 0 row affected
obclient> ALTER TABLE tbl2 ADD INDEX ind1 (c1,c2);
Query OK, 0 row affected
Drop an index
Syntaxes:
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;
When you drop an index from a table, this table still supports read and write operations.
Sample code:
obclient> ALTER TABLE tbl2 DROP INDEX ind1;
Query OK, 0 row affected
obclient> DROP INDEX ind1 ON tbl2;
Query OK, 0 rows affected
RENAME INDEX
Syntax:
ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name;
Sample code:
obclient> ALTER TABLE tbl2 RENAME INDEX ind1 TO ind2;
Query OK, 0 rows affected