Add an index to an existing table. This statement improves query performance by creating a data structure for rapid lookups on specific columns.
The CREATE INDEX statement is used to add indexes to a table. Indexes can be created at the same as the table, with the statement. In some cases, such as for InnoDB primary keys, doing so during creation is preferable, as adding a primary key will involve rebuilding the table.
The statement is mapped to an ALTER TABLE statement to create . See . CREATE INDEX cannot be used to create a ; use ALTER TABLE instead.
If another connection is using the table, a is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.
Another shortcut, , allows the removal of an index.
For valid identifiers to use as index names, see .
For limits on InnoDB indexes, see .
Note that KEY_BLOCK_SIZE is currently ignored in CREATE INDEX, although it is included in the output of .
Executing the CREATE INDEX statement requires the privilege for the table or the database.
Online DDL is supported with the and clauses.
See for more information on online DDL with InnoDB.
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.
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.
See for information about index definitions.
Set the lock wait timeout. See .
See for more information.
See for more information.
MariaDB provides progress reporting for CREATE INDEX statement for clients that support the new progress reporting protocol. For example, if you were using the client, then the progress report might look like this::
The progress report is also shown in the output of the statement and in the contents of the table.
See for more information.
The WITHOUT OVERLAPS clause allows you to constrain a primary or unique index such that cannot overlap. It can be used like this:
WITHOUT OVERLAPS is not available.
Creating a unique index:
OR REPLACE and IF NOT EXISTS:
This page is licensed: GPLv2, originally from
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}CREATE INDEX i ON tab (num);
Stage: 1 of 2 'copy to tmp table' 46% of stageCREATE UNIQUE INDEX u ON rooms (room_number, p WITHOUT OVERLAPS);CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);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' |
+-------+------+-------------------------+