CREATE INDEX
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}
Contents
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.
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.
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' | +-------+------+-------------------------+