All Products
Search
Document Center

CREATE INDEX

Last Updated: Jun 18, 2021

Description

This statement creates an index. An index is a structure that is created on a table to sort the values of one or more columns of the database table. The main function of the index is to improve the query speed and reduce the performance cost of the database system.

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…]

Parameter description

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. Each column name can be followed by ASC that represents the ascending order, and cannot be followed by DESC that represents the descending order. By default, the ascending order is used.

In the process of setting up the sorting method of indexes, records are first sorted by using the values of the first column in index_col_name. The records for which the values in the first column are the same are sorted by using the values in the next column. Similar rules apply to the other records.

index_type

The index type. Only USING BTREE is supported. This indicates that B-tree indexes are used.

UNIQUE

Specifies the index as a unique index.

index_option

Specifies the index option. Separate multiple values of index_option with spaces.

GLOBAL | LOCAL

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

COMMENT

Specifies the comment.

COMPRESSION

Specifies the compression algorithm.

BLOCK_SIZE

Specifies the micro-block size.

STORING

Specifies that some columns are stored in the index table for redundant storage. This improves the query performance of systems.

Examples

  1. Run the following command to create a table that is named test:

CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
  1. Run the following command to create an index on the test table:

CREATE INDEX test_index ON test (c1, c2 DESC);
  1. Run the following command to query the index on the test table:

SHOW INDEX FROM test;