How to Create Indexes In MySQL

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 create a table with a primary key or unique key, MySQL automatically creates an PRIMARY index. This index is called a clustered index .

PRIMARY indexes are special because the index itself is stored in the same table as the data. A clustered index enforces the order of the rows in the table.

Indexes other than PRIMARY indexes are called secondary or nonclustered indexes.

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.

• 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}
  

  ALGORITHM clause is optional. Default is INSTANT. INSTANT used if not supported INPLACE.

  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.
  • INSTANT: The operation only modifies the metadata in the data dictionary. During the execution phase of the operation, an exclusive metadata lock may be briefly held on the table. Table data is unaffected, making operations instantaneous. Concurrent DML is allowed. (introduced in MySQL 8.0.12)

• 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:

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:

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.