Create indexes

You can use the CREATE INDEX statement to create an index on a table.

OceanBase Database allows you to create an index on both partitioned and non-partitioned tables. An index can be either a local index or a global index. In addition, the index can either be a unique index or a normal index. A local unique index on a partitioned table must include a partitioning key of the table.

This topic describes how to create an index on a non-partitioned table. For information about how to create an index on a partitioned table, see Create an index on a partitioned table.

SQL syntax for creating an index:

CREATE [UNIQUE] INDEX index_name ON table_name ( column_list ) [LOCAL | GLOBAL] [ PARTITION BY column_list PARTITIONS N ];

Parameters:

• UNIQUE: specifies that the index to be created is a unique index.

• table_name: the name of the table on which the index is to be created.

• column_list: the one or more columns to be indexed. Separate multiple columns with commas (,).

• LOCAL: specifies that the index to be created is a local index. In MySQL mode, if you do not specify GLOBAL or LOCAL when you create an index, the LOCAL keyword takes effect by default.

• GLOBAL: specifies that the index to be created is a global index.

• PARTITION BY column_list PARTITIONS N: the partitioning method.

Note

Index names must be unique on a table. You can execute the SHOW INDEX statement to query the indexes on a table.

You can also use the following syntax to create an index:

ALTER TABLE  table_name  
ADD INDEX|KEY  index_name ( column_list );

This statement allows you to create multiple indexes at a time. You can use either the INDEX or the KEY keyword.

Example: Create a normal index on a non-partitioned table.

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

obclient> CREATE INDEX idx ON test (c1, c2);
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 | idx      |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| test  |          1 | idx      |            2 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set

Parameters:

• Seq_in_index: the sequence number of the column in the composite index. In this example, the sequence numbers include 1 and 2.

• Column_name: the name of the indexed column.

• Non_unique: The value is 0 if the index cannot contain duplicate values, and is 1 if it can contain duplicate values. This parameter indicates whether the index is a unique index.

• Collation: the way in which columns are stored in the index.

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

• Sub_part: the number of indexed characters if the column is partially indexed. The value is NULL if the entire column is indexed.

• Packed: the way in which keywords are compressed. The value is NULL if keywords are not compressed.

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