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'
 
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
 
In MariaDB 10.0 and later, 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 ...
 
The OR REPLACE clause was added in MariaDB 10.1.4.
 
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 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.
 
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' |
+-------+------+-------------------------+
 


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