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 correct order in the index definition, a single composite index can enhance the performance of queries involving those columns on the same table.

To create a composite index during 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.

Alternatively, you can add a composite index to an existing table 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 of 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;


SELECT
    *
FROM
    table_name
WHERE
    c1 = v1 AND 
    c2 = v2;


SELECT  
    *
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 the 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 the 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)

Summary

• Composite indexes are indexes that involve more than one column.
• Define composite indexes when your queries involve conditions or sorting on multiple columns.
• Using composite indexes properly can significantly improve the performance of queries that filter or sort based on the indexed columns.