CREATE INDEX|V4.1.0| docs|Distributed Database

CREATE INDEX

Last Updated：2023-07-28 02:55:42 Updated

Purpose

You can use this statement to create an index. An index is a database structure created for a table to sort data in one or more columns of the table in a specific order. It improves the query speed and reduces the performance overhead of database systems.

The current version of OceanBase Database supports unique indexes and non-unique indexes. When you create an index, you can use the STORING(column_name,...) clause to specify redundant columns in the index table. The redundant columns of an index table include the columns that you specified and the primary key columns if the table has a primary key or hidden primary key columns if the table does not have a primary key.

Note

In MySQL mode of OceanBase Database, if you delete all index columns in a table, the created index becomes invalid.

Syntax

CREATE [UNIQUE] INDEX index_name
     ON table_name (index_col_name,...) [STORING (column_name,...)];
      [index_type] [index_options]

index_type:
      USING BTREE

index_options:
      index_option [index_option...]

index_option:
    GLOBAL | LOCAL
    | COMMENT 'string'
    | COMPRESSION [=] {NONE | LZ4_1.0 | LZO_1.0 | SNAPPY_1.0 | ZLIB_1.0}
    | BLOCK_SIZE [=] size
    | STORING(column_name_list)
    | VISIBLE | INVISIBLE

index_col_name:
    column_name [(length)] [ASC | DESC]

column_name_list:
    column_name [, column_name...]

Parameters

Parameter	Description
index_name	The name of the index to be created.
table_name	The name of the table to which the index belongs.
index_col_name	The column name of the index. You can add `ASC` (ascending order) to the end of each column name. `DESC` (descending order) is not supported. By default, the columns are sorted in ascending order. Index-based sorting method: Data is first sorted by the values in the first column of `index_col_name` and by the values in the next column for the records with the same values in the first column.
column_name	The name of the column used to create the index.
length	For a string column, you can use the `col_name(length)` syntax to extract part of the string for creating an index. Supported data types: `CHAR`, `VARCHAR`, `BINARY`, and `VARBINARY`.
index_type	The index type. Valid value: `USING BTREE`, which indicates that B-tree indexes can be used.
UNIQUE	Specifies the index to a unique index.
STORING	The redundant columns in the index table.
index_option	The index options. Multiple `index options` are separated with spaces.
GLOBAL \| LOCAL	Specifies whether the index is a global index or a local index. Default value: `LOCAL`.
COMMENT	The comment.
COMPRESSION	The compression algorithm.
BLOCK_SIZE	The microblock size.
STORING	Specifies to store copies of some columns in the index table to improve the query performance of the system.

Examples

Create table test, create test_index on columns c1 and c2, and query the index of table test. Key_name of two lines in the results is test_index. Columns c1 and c2 are index columns, and column c2 is a sub_part of test_index.

obclient>  CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
Query OK, 0 rows affected

obclient> CREATE INDEX test_index ON test (c1, c2 ASC);
Query OK, 0 rows affected

obclient> SHOW INDEX FROM test;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| test  |          0 | PRIMARY    |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| test  |          1 | test_index |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| test  |          1 | test_index |            2 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set

Create a global index.

obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY, col2 INT, col3 INT, INDEX IDX(col2) GLOBAL);
Query OK, 0 row affected

Create a unique index on the tbl2 table.

/* Create a table named tbl2. */
obclient> CREATE TABLE tbl2 (col1 INT, col2 INT, col3 INT, PRIMARY KEY(col1));
Query OK, 0 rows affected

/* Create a unique index on the tbl2 table. */
obclient> CREATE UNIQUE INDEX tbl2_idx2 ON tbl2(col2) STORING(col3);
Query OK, 0 rows affected

/* View the unique index on the tbl2 table. */
obclient> SHOW INDEX FROM tbl2 \G
*************************** 1. row ***************************
        Table: tbl2
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: col1
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment: available
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: tbl2
   Non_unique: 0
     Key_name: tbl2_idx2
 Seq_in_index: 1
  Column_name: col2
    Collation: A
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: available
Index_comment:
      Visible: YES
2 rows in set