Index operations|V4.2.5| docs|Distributed Database

Index operations

Last Updated：2025-11-19 10:08:12 Updated

This topic describes how to perform simple index operations in MySQL mode of OceanBase Database, including creating an index, querying indexes, adding an index, dropping an index, and renaming an index.

Create an index

The two syntaxes for creating an index are as follows:

CREATE INDEX
```
CREATE [SPATIAL | UNIQUE] INDEX [IF NOT EXISTS] index_name 
    [USING BTREE] ON table_name (sort_column_key [, sort_column_key... ]) 
    [index_option...] [partition_option];
```
The parameters are described as follows:
- index_name: the name of the index to be created.
- table_name: the name of the table on which the index is to be created.
- sort_column_key: the column used as the sort key. You can specify multiple columns and separate them with commas (,).
- index_option: the index option. You can specify multiple index options and separate them with spaces.
- partition_option: the index partitioning option.
For more information about the syntax, see CREATE INDEX.

When an index is being created on a table, this table still supports read and write operations. Here are two examples of creating an index:
- Assume that the t1 table exists in the database. Create an index named index1 on the t1 table, with the index sorted in ascending order on the c1 column.
```
obclient> CREATE INDEX index1 ON t1 (c1 ASC);
```
- Assume that the t2 table exists in the database. Create an index named index2 on the t2 table, with c1 + c2 as the index expression.
```
obclient> CREATE INDEX IF NOT EXISTS index2 ON t2 (c1 + c2);
```
CREATE TABLE
```
CREATE TABLE [IF NOT EXISTS] table_name 
    (column_name column_definition,[column_name column_definition,...], 
    {INDEX | KEY} [index_name] [index_type] (key_part,...)
    [index_option...]);
```
The parameters are described as follows:
- table_name: the name of the table to be created.
- column_name: the name of a column in the table.
- column_definition: the data type of the column in the table.
- INDEX | KEY: indicates that you can use the INDEX or KEY keyword in the statement.
- index_name: the name of the index to be created. This parameter is optional. If you do not specify a value for this parameter, the index name is the same as the column name by default.
- index_type: the index type. This parameter is optional.
- key_part: creates a function-based index.
- index_option: the index option. You can specify multiple index options and separate them with spaces.
For more information about the syntax, see CREATE TABLE.

The following sample statement creates a table named t3 and an index named index3 that is sorted in ascending order on the id column.
```
obclient> CREATE TABLE t3 
    (id int, 
    name varchar(50), 
    INDEX index3 (id ASC));
```

Query indexes

You can use SHOW INDEX to query the indexes on a table. The following statement takes the test table as an example.

obclient> SHOW INDEX FROM test;

The output is as follows:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| test    |          0 | PRIMARY  |            1 | id          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
| test    |          1 | index1   |            1 | name        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
| test    |          1 | index2   |            1 | age         | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+

The columns in the query results are described as follows:

Non_unique: If the index cannot include duplicate values, the value of the column is 0. Otherwise, the value is 1. In other words, the value 0 indicates a unique index.
Key_name: the name of the index.
Seq_in_index: the sequence number of the column in the composite index, such as 1 or 2.
Column_name: the name of the indexed column.
Collation: the collation that indicates how columns are stored in the index.
Cardinality: the estimated number of unique values in the index.
Sub_part: indicates a prefixed index. If the column is partially indexed, the value is the number of indexed characters in the column. The value is NULL if the entire column is indexed.
Packed: indicates how the key is packed. If keywords are not compressed, the value is NULL.
Index_type: the index type. At present, only the BTREE type is supported.
Comment: indicates whether the index is available.
Index_comment: the comment on the index.
Visible: indicates whether the index is visible.

Add an index

The syntax for adding an index is as follows:

ALTER TABLE table_name ADD {INDEX | KEY} index_name [index_type] (key_part,...) [index_option];

The parameters are described as follows:

table_name: the name of the table on which the index is to be created.
INDEX | KEY: indicates that you can use the INDEX or KEY keyword in the statement.
index_name: the name of the index to be created.
index_type: the index type. This parameter is optional.
key_part: creates a function-based index.
index_option: the index option. You can specify multiple index options and separate them with spaces.

For more information about the syntax, see ALTER TABLE.

Assume that the t1 table exists in the database. The following sample statement creates an index named index2 on the t1 table, with the index sorted in ascending order on the c2 and c3 columns.

obclient> ALTER TABLE t1 ADD INDEX index2 (c2,c3 ASC);

Drop an index

The two syntaxes for dropping an index are as follows:

DROP INDEX
```
DROP INDEX index_name ON table_name;
```
index_name specifies the name of the index to be dropped, and table_name specifies the name of the table from which the index is to be dropped. For more information about the syntax, see DROP INDEX.
ALTER TABLE
```
ALTER TABLE table_name DROP {INDEX | KEY} index_name;
```
The parameters are described as follows:
- table_name: the name of the table from which the index is to be dropped.
- INDEX | KEY: indicates that you can use the INDEX or KEY keyword in the statement.
- index_name: the name of the index to be dropped.
For more information about the syntax, see ALTER TABLE.

When you drop an index from a table, this table still supports read and write operations.

Assume that the t1 table exists in the database and has an index named index1. The following sample statements drop the index.

DROP INDEX
```
obclient> DROP INDEX index1 ON t1;
```

ALTER TABLE

obclient> ALTER TABLE t1 DROP INDEX index1;

Rename an index

The syntax for renaming an index is as follows:

ALTER TABLE table_name RENAME {INDEX | KEY} old_index_name TO new_index_name;