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 were 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

This statement has supported progress reporting since MariaDB 5.3.

UNIQUE/FULLTEXT/SPATIAL

See details at Unique Index, SPATIAL INDEX and Full-text Indexes.

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.

WAIT/NOWAIT

MariaDB starting with 10.3.0

Set the lock wait timeout. See WAIT and NOWAIT.

Examples

Creating a unique index:

CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);

OR REPLACE and IF NOT EXISTS:

CREATE INDEX xi ON xx5 (x);
Query OK, 0 rows affected (0.03 sec)

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

CREATE OR REPLACE INDEX xi ON xx5 (x);
Query OK, 0 rows affected (0.03 sec)

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...