In MySQL mode of OceanBase Database, you can create an index, add an index, drop an index, and rename an index.
Create an index
The syntax for creating an index is as follows:
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.
Here is an example:
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 an index
The syntax for adding an index is as follows:
ALTER TABLE table_name ADD INDEX index_name (index_col_name,...);
Here is an example:
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
The two syntaxes for dropping an index are as follows:
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.
Here is an example:
obclient> ALTER TABLE tbl2 DROP INDEX ind1;
Query OK, 0 row affected
obclient> DROP INDEX ind1 ON tbl2;
Query OK, 0 rows affected
Rename an index
The syntax for renaming an index is as follows:
ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name;
Here is an example:
obclient> ALTER TABLE tbl2 RENAME INDEX ind1 TO ind2;
Query OK, 0 rows affected