MySQL Composite Index
Summary: in this example, you will learn about the MySQL composite index and how to use it to speed up your queries.
Introduction to MySQL composite index
A composite index is an index on multiple columns. MySQL allows you to create a composite index that consists of up to 16 columns.
A composite index is also known as a multiple-column index.
The query optimizer uses the composite indexes for queries that test all columns in the index, or queries that test the first columns, the first two columns, and so on.
If you specify the columns in the right order in the index definition, a single composite index can speed up these kinds of queries on the same table.
To create a composite index at the time of table creation, you use the following statement:
CREATE TABLE table_name (
c1 data_type PRIMARY KEY,
c2 data_type,
c3 data_type,
c4 data_type,
INDEX index_name (c2,c3,c4)
);
Code language: SQL (Structured Query Language) (sql)
In this syntax, the composite index consists of three columns c2, c3, and c4.
Or you can add a composite index to an existing table by using the CREATE INDEX statement:
CREATE INDEX index_name
ON table_name(c2,c3,c4);
Code language: SQL (Structured Query Language) (sql)
Notice that if you have a composite index on (c1,c2,c3), you will have indexed search capabilities on one the following column combinations:
(c1)
(c1,c2)
(c1,c2,c3)
Code language: SQL (Structured Query Language) (sql)
For example:
SELECT
*
FROM
table_name
WHERE
c1 = v1;*
FROM
table_name
WHERE
c1 = v1 AND
c2 = v2;
*
FROM
table_name
WHERE
c1 = v1 AND
c2 = v2 AND
c3 = v3;
Code language: SQL (Structured Query Language) (sql)
The query optimizer cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. For example, the following queries cannot use the composite for lookups:
SELECT
*
FROM
table_name
WHERE
c1 = v1 AND
c3 = v3;
Code language: SQL (Structured Query Language) (sql)
MySQL composite index example
We will use the employees table in the sample database for the demonstration.
The following statement creates a composite index over the lastName and firstName columns:
CREATE INDEX name
ON employees(lastName, firstName);
Code language: SQL (Structured Query Language) (sql)
First, the name index can be used for lookups in the queries that specify a lastName value because the lastName column is the leftmost prefix of the index.
Second, the name index can be used for queries that specify values for the combination of the lastName and firstName values.
The name index, therefore, is used for lookups in the following queries:
1) Find employees whose last name is Patterson
SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson';
Code language: SQL (Structured Query Language) (sql)
This query uses the name index because the leftmost prefix of the index, which is the lastName column, is used for lookups.
You can verify this by adding the EXPLAIN clause to the query:
EXPLAIN SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson';
Code language: SQL (Structured Query Language) (sql)
Here is the output:
2) Find employees whose last name is Patterson and the first name is Steve:
SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson' AND
firstName = 'Steve';
Code language: SQL (Structured Query Language) (sql)
In this query, both lastName and firstName columns are used for lookups, therefore, it uses the name index.
Let’s verify it:
EXPLAIN SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson' AND
firstName = 'Steve';
Code language: SQL (Structured Query Language) (sql)
The output is:
3) Find employees whose last name is Patterson and first name is Steve or Mary:
SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson' AND
(firstName = 'Steve' OR
firstName = 'Mary');
Code language: SQL (Structured Query Language) (sql)
This query is similar to the second one which both lastName and firstName columns are used for lookups.
The following statement verifies the index usage:
EXPLAIN SELECT
firstName,
lastName,
email
FROM
employees
WHERE
lastName = 'Patterson' AND
(firstName = 'Steve' OR
firstName = 'Mary');
Code language: SQL (Structured Query Language) (sql)
The output is:
The query optimizer cannot use the name index for lookups in the following queries because only the firstName column which is not the leftmost prefix of the index is used:
SELECT
firstName,
lastName,
email
FROM
employees
WHERE
firstName = 'Leslie';
Code language: SQL (Structured Query Language) (sql)
Similarly, the query optimizer cannot use the name index for the lookups in the following query because either the firstName or lastName column is used for lookups.
SELECT
firstName,
lastName,
email
FROM
employees
WHERE
firstName = 'Anthony' OR
lastName = 'Steve';
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL composite index to speed up the queries.