MySQL: Indexes

This tutorial explains how to create, drop, and rename indexes in MySQL with syntax and examples.

What is an Index in MySQL?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

Create an Index

There are 2 ways to create an index. You can either create an index when you first create a table using the CREATE TABLE statement or you can use the CREATE INDEX statement after the table has been created.

Syntax

The syntax to create an index using the CREATE TABLE statement in MySQL is:

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  column_n datatype [ NULL | NOT NULL ],

  INDEX index_name [ USING BTREE | HASH ]
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC])
);

OR

The syntax to create an index using the CREATE INDEX statement in MySQL is:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
  [ USING BTREE | HASH ]
  ON table_name
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC]);

UNIQUE

Optional. The UNIQUE modifier indicates that the combination of values in the indexed columns must be unique.

FULLTEXT

Optional. The FULLTEXT modifier indexes the entire column and does not allow prefixing. InnoDB and MyISAM tables support this option.

SPATIAL

Optional. The SPATIAL modifier indexes the entire column and does not allow indexed columns to contain NULL values. InnoDB (starting in MySQL 5.7) and MyISAM tables support this option.

index_name

The name to assign to the index.

table_name

The name of the table in which to create the index.

index_col1, index_col2, ... index_col_n

The columns to use in the index.

length

Optional. If specified, only a prefix of the column is indexed not the entire column. For non-binary string columns, this value is the given number of characters of the column to index. For binary string columns, this value is the given number of bytes of the column to index.

ASC

Optional. The index is sorted in ascending order for that column.

DESC

Optional. The index is sorted in descending order for that column.

Example

Let's look at an example of how to create an index in MySQL using the CREATE TABLE statement. This statement would both create the table as well as the index at the same time.

For example:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id),
  INDEX contacts_idx (last_name, first_name)
);

In this example, we've created the contacts table as well as an index called contacts_idx which consists of the last_name and first_name columns.

Next, we will show you how to create the table first and then create the index using the CREATE INDEX statement.

For example:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE INDEX contacts_idx
  ON contacts (last_name, first_name);

In this example, the CREATE TABLE statement would create the contacts table. The CREATE INDEX statement would create an index called contacts_idx that consists of the last_name and the first_name fields.

Unique Index

To create a unique index on a table, you need to specify the UNIQUE keyword when creating the index. Again, this can be done with either a CREATE TABLE statement or a CREATE INDEX statement.

For example:

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id),
  UNIQUE INDEX contacts_idx (last_name, first_name)
);

OR

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE UNIQUE INDEX contacts_idx
  ON contacts (last_name, first_name);

Both of these examples would create a unique index on the last_name and first_name fields so that the combination of these fields must always contain a unique value with no duplicates. This is a great way to enforce integrity within your database if you require unique values in columns that are not part of your primary key.

Drop an Index

You can drop an index in MySQL using the DROP INDEX statement.

Syntax

The syntax to drop an index using the DROP INDEX statement in MySQL is:

DROP INDEX index_name
  ON table_name;

index_name

The name of the index to drop.

table_name

The name of the table where the index was created.

Example

Let's look at an example of how to drop an index in MySQL.

For example:

DROP INDEX contacts_idx
  ON contacts;

In this example, we've dropped an index called contacts_idx from the contacts table.

Rename an Index

You can rename an index in MySQL. Depending on your version of MySQL, there are two different syntaxes.

Syntax

The syntax to rename an index using the ALTER TABLE statement in MySQL 5.6 and later is:

ALTER TABLE table_name   DROP INDEX index_name,
  ADD INDEX new_index_name [ USING BTREE | HASH ]
    (index_col1 [(length)] [ASC | DESC], 
     index_col2 [(length)] [ASC | DESC],
     ...
     index_col_n [(length)] [ASC | DESC]);

OR

The syntax to rename an index in MySQL 5.7 or newer is:

ALTER TABLE table_name   RENAME INDEX index_name TO new_index_name;

table_name

The name of the table where the index was created.

index_name

The name of the index that you wish to rename.

new_index_name

The new name for the index.

Example

Let's look at an example of how to rename an index in MySQL. In older versions of MySQL, you need to use the ALTER TABLE statement to first drop the old index and then recreate the new index.

For example (MySQL 5.6 and older):

ALTER TABLE contacts   DROP INDEX contacts_idx,
  ADD INDEX contacts_new_index (last_name, first_name);

In this example, we've renamed the index called contacts_idx to contacts_new_index. This was done by dropping the old index and then adding the new index.

Starting in MySQL 5.7, you can use the ALTER TABLE statement with the RENAME INDEX clause to rename the index.

For example (MySQL 5.7 and newer):

ALTER TABLE contacts   RENAME INDEX contacts_idx TO contacts_new_index;

This would also rename the index from contacts_idx to contacts_new_index. If you are unsure which version of MySQL you are running, it is safest to use the first syntax to rename an index.