Description
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 indexname
ON tblname (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(columname_list)
| VISIBLE | INVISIBLE
index_col_name:
colname [(length)] [ASC | DESC]
columname_list:
colname [, colname...]
Parameters
| Parameter | Description |
|---|---|
| indexname | Specifies the name of the index to be created. |
| tblname | 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. |
| index_type | Specifies the index type. Only USING BTREE is supported, with a B-tree used as the index. |
| 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 GLOBAL. |
| 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
- Run the following command to create Table test:
obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
- Run the following command to create an index for Table test:
obclient> CREATE INDEX test_index ON test (c1, c2 ASC);
- Run the following command to view the index of Table test:
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 (0.05 sec)