Index operations

This topic describes how to create, view, add, drop, and rename indexes in OceanBase Database in MySQL mode.

Create an index

You can create an index in the following ways:

• 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 in the preceding statement are described as follows:

  • index_name specifies the name of the index to be created.

  • table_name specifies the name of the table in which the index is to be created.

  • sort_column_key specifies a sorting column. You can specify multiple sorting columns when you create an index. Multiple columns are separated with commas (,).

  • index_option specifies index options. You can specify multiple index options when you create an index. Multiple options are separated with spaces.

  • partition_option specifies partitioning options.

  For more information about the syntax, see CREATE INDEX.

  A table remains available for read and write operations while an index is being created. Here is an example:

  • Assume that the t1 table exists in the database. Create an index named index1 on the c1 column in the t1 table and specify that the index sorts values in ascending order.

    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 and specify the expression 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 in the preceding statement are described as follows:

  • table_name specifies the name of the table to be created.

  • column_name specifies a column in the table.

  • column_definition specifies the data type for each column in the table.

  • INDEX | KEY specifies the INDEX or KEY keyword.

  • index_name specifies the name of the index to be created. If you do not specify this parameter, the index name is the same as the column name.

  • index_type specifies the index type. This parameter is optional.

  • key_part specifies a function index.

  • index_option specifies index options. If you specify multiple options, separate them with spaces.

  For more information about the syntax, see CREATE TABLE.

  The following example shows how to create the t3 table and create an index named index3 on the id column of the t3 table.

  obclient> CREATE TABLE t3
      (id int,
      name varchar(50),
      INDEX index3 (id ASC));
  

Query indexes

You can execute the SHOW INDEX statement to query the indexes in a table. The following example shows how to query the indexes in the test table:

obclient> SHOW INDEX FROM test;

Here is the result:

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| 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 following table describes the relevant fields in the query result.

• Non_unique: 0 indicates that the index cannot contain duplicate values. 1 indicates the opposite. That is, the index is unique.

• Key_name: the name of the index.

• Seq_in_index: the sequence number of a column in an index. 1 or 2 indicates the sequence of the column in the index. In a composite index, multiple columns are included.

• Column_name: the name of a column in the index.

• Collation: the way in which a column is stored in the index.

• Cardinality: the number of unique values in the index. This value is an estimate.

• Sub_part: the number of characters indexed. If a prefix of the column is indexed, this value indicates the length of the prefix. NULL indicates that the entire column is indexed.

• Packed: indicates how keywords are compressed. NULL indicates that the column is not compressed.

• Index_type: the type of the index. Currently, only the BTREE type is supported.

• Comment: whether the index is available.

• Index_comment: the comment information of the index.

• Visible: whether the index is visible.

Add an index

The syntax 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 specifies the name of the table to which the index belongs.

• INDEX | KEY specifies the INDEX or KEY keyword.

• index_name specifies the name of the index to be created.

• index_type specifies the index type. This parameter is optional.

• key_part specifies a function index.

• index_option specifies index options. If you specify multiple options, separate them with spaces.

For more information about the syntax, see ALTER TABEL.

Assume that the t1 table exists in the database. The following example shows how to add an index named index2 to the c2 and c3 columns in the t1 table. In this example, the index sorts values in ascending order.

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

Delete an index

You can use the following statements to delete an index:

• DROP INDEX

  DROP INDEX index_name ON table_name;
  

  In the preceding statement, index_name specifies the name of the index to be deleted, and table_name specifies the name of the table in which the index is located. 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 specifies the name of the table in which the index is located.

  • INDEX | KEY specifies the INDEX or KEY keyword.

  • index_name specifies the name of the index to be deleted.

  For more information about the syntax, see ALTER TABLE.

A table remains available for read and write operations while an index is being deleted.

Assume that the t1 table exists in the database and that the index1 index exists in the t1 table. The following examples show how to delete the index1 index from the t1 table.

• 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;

The following table describes the related parameters:

• table_name: specifies the name of the table that contains the index to be renamed.

• INDEX | KEY: specifies the keyword INDEX or KEY in the statement.

• old_index_name: specifies the name of the index to be renamed.

• new_index_name: specifies the new name of the index.

For more information about the syntax, see ALTER TABEL.

Assume that the database contains a table named t1 and an index named index2 on the t1 table. The following example renames the index2 index to index3:

obclient> ALTER TABLE t1 RENAME INDEX index2 TO index3;

References

• Index overview

• Create and manage indexes