> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-index.md).

# CREATE INDEX

## Syntax

```bnf
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}
```

![Railroad diagram of CREATE INDEX — equivalent to the BNF above](/files/0txZVGe6V58XcvB5bh48)

![Railroad diagram of index\_col\_name](/files/4ViFyieiPD7lzpD4yjno)

![Railroad diagram of index\_type](/files/mGl3uKwc8Uy1bsi07AH9)

![Railroad diagram of index\_option](/files/BPhHZrKUyq5Jv9FCMDHn)

![Railroad diagram of algorithm\_option](/files/yt1czMdJ5gDYB4UksdsP)

![Railroad diagram of lock\_option](/files/3CR7dOzRMRQeKVvwSqeA)

## 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](/docs/server/server-usage/tables/create-table.md) 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](/docs/server/mariadb-quickstart-guides/mariadb-indexes-guide.md). See [ALTER TABLE](/docs/server/reference/sql-statements/data-definition/alter/alter-table.md). `CREATE INDEX` cannot be used to create a [PRIMARY KEY](/docs/server/mariadb-quickstart-guides/mariadb-indexes-guide.md#primary-key); use `ALTER TABLE` instead.

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

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

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

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

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

## Privileges

Executing the `CREATE INDEX` statement requires the [INDEX](/docs/server/reference/sql-statements/account-management-sql-statements/grant.md) 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](/docs/server/server-usage/storage-engines/innodb/innodb-online-ddl.md) 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](/docs/server/server-usage/tables/create-table.md#index-definitions) for information about index definitions.

## WAIT/NOWAIT

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

## ALGORITHM

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

## LOCK

See [ALTER TABLE: LOCK](/docs/server/reference/sql-statements/data-definition/alter/alter-table.md#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](/docs/server/clients-and-utilities/mariadb-client.md) 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](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-processlist.md) statement and in the contents of the [information\_schema.PROCESSLIST](/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-processlist-table.md) table.

See [Progress Reporting](/docs/server/reference/product-development/mariadb-internals/using-mariadb-with-your-programs-api/progress-reporting.md) 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](/docs/server/reference/sql-structure/temporal-tables/application-time-periods.md) 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](/docs/server/reference/sql-structure/sql-language-structure/identifier-names.md)
* [Getting Started with Indexes](/docs/server/mariadb-quickstart-guides/mariadb-indexes-guide.md)
* [ALTER TABLE](/docs/server/reference/sql-statements/data-definition/alter/alter-table.md)
* [DROP INDEX](/docs/server/reference/sql-statements/data-definition/drop/drop-index.md)
* [SHOW INDEX](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-index.md)
* [SPATIAL INDEX](/docs/server/reference/sql-structure/geometry/spatial-index.md)
* [Full-text Indexes](/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/full-text-indexes.md)
* [WITHOUT OVERLAPS](#without-overlaps)
* [Ignored Indexes](/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/ignored-indexes.md)
* [InnoDB Limitations](/docs/server/server-usage/storage-engines/innodb/innodb-limitations.md)

<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" %}