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:
SELECT
phone_number
FROM
phonebooks
WHERE
first_name = 'Bob' AND
last_name = 'Cat';
Code language: SQL (Structured Query Language) (sql)
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.
CREATE TABLE t(
c1 INT PRIMARY KEY,
c2 INT NOT NULL,
c3 INT NOT NULL,
c4 VARCHAR(10),
INDEX (c2,c3)
);
Code language: SQL (Structured Query Language) (sql)
To add an index for a column or a set of columns, you use the CREATE INDEX
statement as follows:
CREATE INDEX index_name ON table_name (column_list)
Code language: SQL (Structured Query Language) (sql)
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:
CREATE INDEX idx_c4 ON t(c4);
Code language: SQL (Structured Query Language) (sql)
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
:
SELECT
employeeNumber,
lastName,
firstName
FROM
employees
WHERE
jobTitle = 'Sales Rep';
Code language: SQL (Structured Query Language) (sql)
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:
CREATE INDEX jobTitle ON employees(jobTitle);
Code language: SQL (Structured Query Language) (sql)
And execute the above statement again:
EXPLAIN SELECT
employeeNumber,
lastName,
firstName
FROM
employees
WHERE
jobTitle = 'Sales Rep';
Code language: SQL (Structured Query Language) (sql)
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:
SHOW INDEXES FROM employees;
Code language: SQL (Structured Query Language) (sql)
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.