Syntax
------

CREATE [OR REPLACE] [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'
 | CLUSTERING={YES| NO} ]
 [ IGNORED | NOT IGNORED ]

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

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.

For valid identifiers to use as index names, see Identifier Names.

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

Privileges
----------

Executing the CREATE INDEX statement requires the INDEX privilege for the
table or the database.

Online DDL
----------

Online DDL is supported with the ALGORITHM and LOCK clauses.

See InnoDB Online DDL Overview for more information on online DDL with InnoDB.

CREATE OR REPLACE INDEX
-----------------------

If the OR REPLACE clause is used and if the index already exists, then instead
of returning an error, the server will drop the existing index and replace it
with the newly defined index.

CREATE INDEX IF NOT EXISTS
--------------------------

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

Index Definitions
-----------------

See CREATE TABLE: Index Definitions for information about index definitions.

WAIT/NOWAIT
-----------

Set the lock wait timeout. See WAIT and NOWAIT.

ALGORITHM
---------

See ALTER TABLE: ALGORITHM for more information.

LOCK
----

See ALTER TABLE: LOCK for more information.

Progress Reporting
------------------

MariaDB provides progress reporting for CREATE INDEX statement for clients
that support the new progress reporting protocol. For example, if you were
using the mysql client, then the progress report might look like this::

CREATE INDEX i ON tab (num);
Stage: 1 of 2 'copy to tmp table'    46% of stage

The progress report is also shown in the output of the SHOW PROCESSLIST
statement and in the contents of the information_schema.PROCESSLIST table.

See Progress Reporting for more information.

WITHOUT OVERLAPS
----------------

MariaDB starting with 10.5.3
----------------------------
The WITHOUT OVERLAPS clause allows one to constrain a primary or unique index
such that application-time periods cannot overlap.

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' |
+-------+------+-------------------------+

From MariaDB 10.5.3, creating a unique index for an application-time period
table with a WITHOUT OVERLAPS constraint:

CREATE UNIQUE INDEX u ON rooms (room_number, p WITHOUT OVERLAPS);

URL: https://mariadb.com/kb/en/create-index/