# CREATE INDEX

## Syntax

```sql
CREATE [OR REPLACE] [UNIQUE|FULLTEXT|SPATIAL|VECTOR] 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 ]
  | DISTANCE={EUCLIDEAN| COSINE} ]
  | M=number ]

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

lock_option:
    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
```

## Description

The `CREATE INDEX` statement is used to add indexes to a table. Indexes can be created at the same as the table, with the [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) 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 [indexes](https://mariadb.com/docs/server/server-usage/tables/mariadb-indexes-guide-1). See [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table). `CREATE INDEX` cannot be used to create a [PRIMARY KEY](https://mariadb.com/docs/server/mariadb-quickstart-guides/mariadb-indexes-guide#primary-key); use `ALTER TABLE` instead.

If another connection is using the table, a [metadata lock](https://mariadb.com/docs/server/reference/sql-statements/transactions/metadata-locking) is active, and this statement will wait until the lock is released. This is also true for non-transactional tables.

Another shortcut, [DROP INDEX](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-index), allows the removal of an index.

For valid identifiers to use as index names, see [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names).

For limits on InnoDB indexes, see [InnoDB Limitations](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-limitations).

Note that `KEY_BLOCK_SIZE` is currently ignored in `CREATE INDEX`, although it is included in the output of [SHOW CREATE TABLE](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-create-table).

## Privileges

Executing the `CREATE INDEX` statement requires the [INDEX](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/grant) privilege for the table or the database.

## Online DDL

Online DDL is supported with the [ALGORITHM](#algorithm) and [LOCK](#lock) clauses.

See [InnoDB Online DDL Overview](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl) 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](https://mariadb.com/docs/server/server-usage/tables/create-table#index-definitions) for information about index definitions.

## WAIT/NOWAIT

Set the lock wait timeout. See [WAIT and NOWAIT](https://mariadb.com/docs/server/reference/sql-statements/transactions/wait-and-nowait).

## ALGORITHM

See [ALTER TABLE: ALGORITHM](https://mariadb.com/docs/server/reference/sql-statements/alter/alter-table#algorithm) for more information.

## LOCK

See [ALTER TABLE: LOCK](https://mariadb.com/docs/server/reference/sql-statements/alter/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 [mariadb](https://mariadb.com/docs/server/clients-and-utilities/mariadb-client) client, then the progress report might look like this::

```sql
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](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-processlist) statement and in the contents of the [information\_schema.PROCESSLIST](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-processlist-table) table.

See [Progress Reporting](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/broken-reference) for more information.

## WITHOUT OVERLAPS

{% tabs %}
{% tab title="Current" %}
The `WITHOUT OVERLAPS` clause allows you to constrain a primary or unique index such that [application-time periods](https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/application-time-periods) cannot overlap. It can be used like this:

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

{% endtab %}

{% tab title="< 10.5.3" %}
`WITHOUT OVERLAPS` is not available.
{% endtab %}
{% endtabs %}

## Examples

Creating a unique index:

```sql
CREATE UNIQUE INDEX HomePhone ON Employees(Home_Phone);
```

OR REPLACE and IF NOT EXISTS:

```sql
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

* [Identifier Names](https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/identifier-names)
* [Getting Started with Indexes](https://mariadb.com/docs/server/mariadb-quickstart-guides/mariadb-indexes-guide)
* [ALTER TABLE](https://mariadb.com/docs/server/reference/sql-statements/data-definition/alter/alter-table)
* [DROP INDEX](https://mariadb.com/docs/server/reference/sql-statements/data-definition/drop/drop-index)
* [SHOW INDEX](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-index)
* [SPATIAL INDEX](https://mariadb.com/docs/server/reference/sql-structure/geometry/spatial-index)
* [Full-text Indexes](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/full-text-indexes)
* [WITHOUT OVERLAPS](#without-overlaps)
* [Ignored Indexes](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/ignored-indexes)
* [InnoDB Limitations](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-limitations)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}