CREATE INDEX

You are viewing an old version of this article. View the current version here.

Syntax

CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX [IF NOT EXISTS] index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

algorithm_option:
    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Description

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See ALTER TABLE. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead.

If another connection is using the table, a metadata lock is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.

Another shortcut, DROP INDEX, allows the removal of an index.

Note that KEY_BLOCK_SIZE is currently ignored in CREATE INDEX, altough it is included in the output of SHOW CREATE TABLE.

MariaDB starting with 10.0

The ONLINE and OFFLINE clauses have been removed in MariaDB 10.0.

MariaDB starting with 5.5

As of MariaDB 5.5, a comment of up to 1024 characters is permitted with the COMMENT 'string' index option.

MariaDB starting with 5.3

Since MariaDB 5.3 this statement supports progress reporting.

Index type

See Storage Engine Index Types for details on permitted index_types for each storage engine.

Examples

Creating a unique index:

CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.