Index operations|V4.3.1| docs|Distributed Database

Index operations

Last Updated：2024-06-28 05:30:30 Updated

This topic describes how to perform simple index operations in the 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 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];
```
where
- index_name specifies the name of the index to be created.
- table_name specifies the name of the table on which the index is to be created.
- sort_column_key specifies the column as the sort key. You can specify multiple columns and separate them with commas (,).
- index_option specifies the index option. You can specify multiple index options and separate them with spaces.
- partition_option specifies 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 table t1 exists in the database. Create an index named index1 on table t1 and with the index in ascending order on column c1.
```
obclient> CREATE INDEX index1 ON t1 (c1 ASC);
```
- Assume that table t2 exists in the database. Create an index named index2 on table t2 and 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...]);
```
where
- table_name specifies the name of the table to be created.
- column_name specifies the name of the index to be created.
- column_definition specifies the data type of the column in the table.
- INDEX | KEY specifies that either INDEX or KEY can be used as the index keyword.
- index_name specifies 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 specifies the index type. This parameter is optional.
- key_part specifies to create a function-based index.
- index_option specifies 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 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 query result 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 fields in the query result are described as follows:

Non_unique: If the index cannot include duplicate values, the value of the field 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: the compression method of keywords. 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 for 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];

where

table_name specifies the name of the table to which the index is to be added.
INDEX | KEY specifies that either INDEX or KEY can be used as the index keyword.
index_name specifies the name of the index to be added.
index_type specifies the index type. This parameter is optional.
key_part specifies to add a function-based index.
index_option specifies the index option. You can specify multiple index options and separate them with spaces.

For more information about the syntax, see ALTER TABEL.

Assume that table t1 exists in the database. The following sample statement creates an index named index2 on the t1 table and specifies the index 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;
```
Here, index_name specifies the name of the index to be dropped, and table_name specifies the name of the table therefrom which the index is to be dropped. For more information about the syntax, see DROP INDEX.
ALTER TABEL
```
ALTER TABLE table_name DROP {INDEX | KEY} index_name;
```
where
- table_name specifies the name of the table from which the index is to be dropped.
- INDEX | KEY specifies that either INDEX or KEY can be used as the index keyword.
- index_name specifies the name of the index to be dropped.
For more information about the syntax, see ALTER TABEL.

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

Assume that the table t1 exists in the database and has an index named index1. The following sample statement drops 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;