MySQL CREATE INDEX

Summary: in this tutorial, you will learn about the index and how to use the MySQL CREATE INDEX statement to add an index to a table.

The phone book analogy

Suppose you have a phone book that contains all the names and phone numbers of people in a city. Let’s say you want to find Bob Cat’s phone number. Knowing that the names are alphabetically ordered, you first look for the page where the last name is Cat, then you look for Bob and his phone number.

Now, if the names in the phone book were not sorted alphabetically, you would need to go through all pages, reading every name on it until you find Bob Cat. This is called sequential searching. You go over all the entries until you find the person with the phone number that you are looking for.

Relating the phone book to the database table, if you have the table phonebooks and you have to find the phone number of Bob Cat, you would perform the following query:

1 2 3 4 5 6 7

SELECT     phone_number FROM     phonebooks WHERE     first_name = 'Bob' AND     last_name = 'Cat';

It is pretty easy. Although the query is fast, the database has to scan all the rows of the table until it finds the row. If the table has millions of rows, without an index, the data retrieval would take a lot of time to return the result.

Introduction to index

An index is a data structure such as B-Tree that improves the speed of data retrieval on a table at the cost of additional writes and storage to maintain it.

The query optimizer may use indexes to quickly locate data without having to scan every row in a table for a given query.

When you create a table with a primary key or unique key, MySQL automatically creates a special index named PRIMARY. This index is called the clustered index.

The PRIMARY index is special because the index itself is stored together with the data in the same table. The clustered index enforces the order of rows in the table.

Other indexes other than the PRIMARY index are called secondary indexes or non-clustered indexes.

MySQL CREATE INDEX statement

Typically, you create indexes for a table at the time of creation. For example, the following statement creates a new table with an index that consists of two columns c2 and c3.

1 2 3 4 5 6 7

CREATE TABLE t(    c1 INT PRIMARY KEY,    c2 INT NOT NULL,    c3 INT NOT NULL,    c4 VARCHAR(10),    INDEX (c2,c3) );

To add an index for a column or a set of columns, you use the CREATE INDEX statement as follows:

1	CREATE INDEX index_name ON table_name (column_list)

To create an index for a column or a list of columns, you specify the index name, the table to which the index belongs, and the column list.

For example, to add a new index for the column c4, you use the following statement:

1	CREATE INDEX idx_c4 ON t(c4);

By default, MySQL creates the B-Tree index if you don’t specify the index type. The following shows the permissible index type based on the storage engine of the table:

Storage Engine	Allowed Index Types
InnoDB	BTREE
MyISAM	BTREE
MEMORY/HEAP	HASH, BTREE

Notice that the CREATE INDEX statement above is a simplified version of the CREATE INDEX statement introduced by MySQL. We will cover more options in the subsequent tutorials.

MySQL CREATE INDEX example

The following statement finds employees whose job title is Sales Rep:

1 2 3 4 5 6 7 8

SELECT     employeeNumber,     lastName,     firstName FROM     employees WHERE     jobTitle = 'Sales Rep';

Here is the output:

We have 17 rows indicating that 17 employees whose job title is the Sales Rep.

To see how MySQL internally performed this query, you add the EXPLAIN clause at the beginning of the SELECT statement as follows:

As you can see, MySQL had to scan the whole table which consists of 23 rows to find the employees with the Sales Rep job title.

Now, let’s create an index for the  jobTitle column by using the CREATE INDEX statement:

1	CREATE INDEX jobTitle ON employees(jobTitle);

And execute the above statement again:

1 2 3 4 5 6 7 8

EXPLAIN SELECT     employeeNumber,     lastName,     firstName FROM     employees WHERE     jobTitle = 'Sales Rep';

The output is:

As you can see, MySQL just had to locate 17 rows from the  jobTitle index as indicated in the key column without scanning the whole table.

To show the indexes of a table, you use the SHOW INDEXES statement, for example:

1	SHOW INDEXES FROM employees;

Here is the output:

In this tutorial, you have learned about MySQL index and how to add an index for a column in a table.