Index operations

This topic describes simple index operations in OceanBase Database in MySQL mode, including creating indexes, viewing indexes, adding indexes, dropping indexes, and renaming indexes.

Create an index

You can create an index by using the following methods:

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

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

View an index

You can execute the SHOW INDEX statement to view the indexes of a table. 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 related fields in the query result are described as follows:

• Non_unique: the value of this field is 0 if the index cannot contain duplicate values, and 1 otherwise. This field indicates whether the index is a unique index.

• Key_name: the name of the index.

• Seq_in_index: the sequence number of the column in the index. The value of this field is 1 or 2, indicating the order of the index in a composite index.

• Column_name: the name of the indexed column.

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

• Cardinality: the estimated number of unique values in the index.

• Sub_part: the number of characters in the indexed column that are included in the prefix index. If the entire column is included in the index, the value of this field is NULL.

• Packed: the way in which the keywords are compressed. If the keywords are not compressed, the value of this field is NULL.

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

• Comment: indicates whether the index is available.

• Index_comment: the comments 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 to which the index is to be added.

• INDEX | KEY: the INDEX or KEY keyword can be used in this statement.

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

• index_type: the type of the index. This parameter is optional.

• key_part: specifies the columns on which a (function) index is created.

• index_option: specifies the index options. When you specify multiple index_option parameters, separate them with spaces.

For more information about the syntax, see ALTER TABEL.

Assume that the database contains a table named t1. Add an index named index2 to this table and sort the data in the c2 and c3 columns in ascending order. The sample code is as follows:

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

Drop an index

You can use the following syntaxes to drop an index:

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

   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.

You can drop an index while the corresponding table is still used for read and write operations.

Assume that the database contains a table named t1, which has an index named index1. The sample code for dropping the index is as follows:

• 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 parameters are described as follows:

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

• INDEX | KEY: the INDEX or KEY keyword can be used in this statement.

• old_index_name: the name of the index before the rename operation.

• new_index_name: the name of the index after the rename operation.

For more information about the syntax, see ALTER TABEL.

Assume that the database contains a table named t1, which has an index named index2. Rename the index to index3. The sample code is as follows:

obclient> ALTER TABLE t1 RENAME INDEX index2 TO index3;

References

• Overview of indexes

• Create and manage indexes