Syntax

CREATE [OR REPLACE] [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX [IF NOT EXISTS] index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [WAIT n | NOWAIT]
    [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, although 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.

OR REPLACE

MariaDB starting with 10.1.4

The OR REPLACE clause was added in MariaDB 10.1.4

If used and the index already exists, instead of an error being returned, the existing index will be dropped and replaced by the newly defined index.

IF NOT EXISTS

If the IF NOT EXISTS clause is used, the index will only be created if the index of the same name does not exist. If the index already exists, by default a warning will be triggered.

MariaDB starting with 10.3.0

WAIT/NOWAIT

Set the lock wait timeout. See WAIT and NOWAIT.

Examples

Creating a unique index:

CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);

The IF NOT EXISTS clause:

CREATE INDEX xi ON xx5 (x);

CREATE INDEX xi ON xx5 (x);
ERROR 1061 (42000): Duplicate key name 'xi'

CREATE INDEX IF NOT EXISTS xi ON xx5 (x);
Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS;
+-------+------+-------------------------+
| Level | Code | Message                 |
+-------+------+-------------------------+
| Note  | 1061 | Duplicate key name 'xi' |
+-------+------+-------------------------+

See also

Comments

Comments loading...