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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/data-definition/create/create-index.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.