Menu

MySQL CREATE INDEX: Syntax and Examples

Learn MySQL CREATE INDEX syntax, choose indexed columns, create unique indexes, inspect query plans, and resolve Error 1061 for duplicate index names.

Indexes can improve the speed of data retrieval from a table. In a MySQL database, you can add an index to a table using the CREATE INDEX statement.

For a table with a large number of rows, if it is slow to retrieve data based on a certain query condition, it may be because you have not created an index on the column related to the query condition.

An index is similar to a table of contents in a dictionary. If you want to look up a word in the dictionary, the correct way is to check the table of contents first, and then follow the instructions to find the relevant word on the specified page. Proper indexes can significantly increase the speed of retrieving rows from database tables.

MySQL allows you to create indexes on specified columns in a specified table using the CREATE INDEX statement.

Introduction

An index is a data structure, such as a B-Tree, that increases the speed of data retrieval from a table, but requires additional writes and storage to maintain it.

The query optimizer can use indexes to quickly locate data without having to scan all rows in the table for a given query.

When you declare a primary key or unique key in CREATE TABLE, MySQL creates the corresponding index. The primary-key index is named PRIMARY; a unique key has its own index name.

The clustered-index description applies specifically to InnoDB. InnoDB uses the primary key as its clustered index, which stores the row data. If a table has no primary key, InnoDB uses its first UNIQUE index whose key columns are all NOT NULL; if it has no such index, InnoDB creates a hidden clustered index. Other storage engines have different layouts; for example, MyISAM does not support clustered indexes. See the InnoDB clustered-index documentation and MyISAM feature list.

In InnoDB, indexes other than the clustered index are secondary indexes. Each secondary-index record also stores the table’s primary-key columns, which InnoDB uses to find the row in the clustered index.

MySQL CREATE INDEX syntax

You should use the following syntax of CREATE INDEX to add an index to a table:

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

Explanation:

  • The UNIQUE keyword indicates that this index is a unique index. It is optional.

  • index_name is the name of the index. There should not be two indexes with the same name in a table.

If MySQL returns Error 1061 while creating an index, inspect the existing names and definitions with SHOW INDEX before repeating the statement. See MySQL Error 1061 troubleshooting.

  • table_name is the name of the table.

  • column_list is the column names in the table. Multiple column names are separated by commas.

  • The USING clause specifies the type of index. Possible values: BTREE, HASH. It is optional.

  • algorithm_option specifies the algorithm for creating indexes. It uses the following syntax:

    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

    In MySQL 9.7, the ALGORITHM clause accepts DEFAULT, INPLACE, or COPY; INSTANT is not an ALGORITHM value for CREATE INDEX. When the clause is omitted or set to DEFAULT, MySQL selects an algorithm supported by the storage engine and operation. For InnoDB, adding a secondary index is an in-place operation that permits concurrent DML, but it is not instant. If INPLACE is unavailable for an operation, MySQL can use COPY. Check the online DDL support for your table engine and server version before relying on a particular algorithm or lock level. See the MySQL 9.7 CREATE INDEX reference and InnoDB index-operation matrix.

    Using DEFAULT and omitting the ALGORITHM clause has the same effect.

    The following is a description of each algorithm:

    • COPY: Operate on the copy of the original table, and copy the table data in the original table to the new table row by row. Concurrent DML is not allowed.
    • INPLACE: The operation avoids copying table data, but may rebuild the table in-place. Exclusive metadata locks on tables may be briefly held during the preparation and execution phases of an operation. In general, concurrent DML is supported.

  • lock_option Specifies the concurrency control strategy for creating indexes. It uses the following syntax:

    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

    LOCK clause is optional. The following are descriptions of each concurrency strategy:

    DEFAULT

    The maximum concurrency level for the given ALGORITHM clause (if any) and ALTER TABLE operation: Concurrent reads and writes are allowed, if supported. If not, concurrent reads are allowed (if supported). If not, exclusive access is enforced.

    NONE

    Allows concurrent reads and writes if supported. Otherwise, an error will occur.

    SHARED

    If supported, allow concurrent reads but block writes. Writes are blocked even if the storage engine supports concurrent writes for the given ALGORITHM clause (if any) and operation. ALTER TABLE An error occurs if concurrent reads are not supported.

    EXCLUSIVE

    Enforce exclusive access. This is done even if the storage engine supports concurrent read/write for the given ALGORITHM clause (if any) and operation.ALTER TABLE

Internally in MySQL, CREATE INDEX statements are mapped to ALTER TABLE ... ADD INDEX ... statements.

MySQL index types

By default, if you do not specify an index type, MySQL will create a B-Tree index. The following shows the index types allowed for table-based storage engines:

storage engine allowed index types
InnoDB BTREE
MyISAM BTREE
MEMORY/HEAP HASH,BTREE

MySQL CREATE INDEX instance

In the following examples, we use the actor table from the Sakila sample database for demonstration.

The following statement finds an actor with first name NICK:

SELECT * FROM sakila.actor WHERE first_name = 'NICK';

Here’s the output:

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|       44 | NICK       | STALLONE  | 2006-02-15 04:34:33 |
|      166 | NICK       | DEGENERES | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

You can use EXPLAIN to view the execution plan of the above SELECT statement to understand how MySQL executes this query internally, as follows:

For a detailed guide to estimated plans and actual execution timings, see MySQL EXPLAIN and EXPLAIN ANALYZE.

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | actor | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  201 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

As you can see, MySQL has to scan the entire table consisting of 201 rows to find a row with a matching condition.

Now, let’s create an index CREATE INDEX on this first_name column using the following statement:

CREATE INDEX first_name ON actor(first_name);

Here, we have created an index on the actor table for the first_name column with the name first_name.

To see whether the index was created successfully, use the following SHOW INDEXES statement display actor the index of the table, for example:

SHOW INDEXES FROM actor;

Here’s the output:

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| actor |          0 | PRIMARY             |            1 | actor_id    | A         |         201 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | idx_actor_last_name |            1 | last_name   | A         |         122 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | first_name          |            1 | first_name  | A         |         129 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Then, execute the above EXPLAIN statement:

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';

The output is:

+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | actor | NULL       | ref  | first_name    | first_name | 182     | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

As you can see, MySQL only needs to locate 3 of the rows from the first_name indicated index without scanning the entire table.

Conclusion

In MySQL, indexes can improve the efficiency of querying data from tables. You can create indexes for tables using CREATE INDEX.