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.
Syntax
CREATE [UNIQUE] INDEX index_name
ON table_name (index_col_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 | Specifies the name of the index to be created. |
| table_name | Specifies the name of the table to which the index belongs. |
| index_col_name | Specifies 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 | Specifies the column name 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 | Specifies the index type. Valid value: USING BTREE, which indicates that B-tree indexes can be used. |
| UNIQUE | Specifies the unique index. |
| index_option | Specifies the index options. Multiple index options are separated with spaces. |
| GLOBAL LOCAL | Specifies whether the index is a global index or a local index. The default value is LOCAL. |
| COMMENT | Specifies the comment. |
| COMPRESSION | Specifies the compression algorithm. |
| BLOCK_SIZE | Specifies the size of the micro block. |
| STORING | Specifies to store copies of some columns in the index table to improve the query performance of the system. |
Examples
Create table
testandtest_indexon columnsc1andc2, and query the index of tabletest.Key_nameof two lines in the results istest_index. Columnsc1andc2are indexed columns, and columnc2is asub_partoftest_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