All Products
Search
Document Center

CREATE INDEX

Last Updated: Jun 18, 2021

Description

You can execute the CREATE INDEX statement to create an index. Indexes are created on tables to sort the values of one or more table columns. Indexes are used to reduce the query response time and 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

The name of the index that you want to create.

tblname

The name of the table to which the index belongs.

index_col_name

The name of the column on which the index is created. You can specify ASC that follows each column name. ASC indicates that the values are sorted in ascending order. DESC is not supported. By default, the values are sorted in ascending order.

In the CREATE INDEX statement, the values of the first column in index_col_name are indexed. If the values in the first column are the same, the values in the next column are indexed. Similar rules apply to the other columns.

index_type

The type of the index. Set the value to USING BTREE.

UNIQUE

The unique index.

index_option

The index option. To specify multiple index options, separate them with spaces.

GLOBAL | LOCAL

Specifies whether the index is a global or local index. Default value: GLOBAL.

COMMENT

The comments.

COMPRESSION

The compression algorithm.

BLOCK_SIZE

The size of a micro-block.

STORING

Some columns are stored in the indexed table for redundant storage. This improves the system query performance.

Examples

  1. Execute the following statement to create the test table:
CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
  1. Execute the following statement to create indexes on the test table:
CREATE INDEX test_index ON test (c1, c2 DESC);
  1. Execute the following statement to view the indexes of the test table:
SHOW INDEX FROM test;