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:

1 2 3 4 5 6 7

CREATE TABLE table_name (     c1 data_type PRIMARY KEY,     c2 data_type,     c3 data_type,     c4 data_type,     INDEX index_name (c2,c3,c4) );

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:

1 2	CREATE INDEX index_name ON table_name(c2,c3,c4);

Notice that if you have a composite index on (c1,c2,c3), you will have indexed search capabilities on one the following column combinations:

1 2 3

(c1) (c1,c2) (c1,c2,c3)

For example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

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;

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:

1 2 3 4 5 6 7

SELECT     * FROM     table_name WHERE     c1 = v1 AND     c3 = v3;

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:

1 2	CREATE INDEX name ON employees(lastName, firstName);

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

1 2 3 4 5 6 7 8

SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson';

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:

1 2 3 4 5 6 7 8

EXPLAIN SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson';

Here is the output:

2) Find employees whose last name is Patterson and the first name is Steve:

1 2 3 4 5 6 7 8 9

SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson' AND     firstName = 'Steve';

In this query, both lastName and firstName columns are used for lookups, therefore, it uses the name index.

Let’s verify it:

1 2 3 4 5 6 7 8 9

EXPLAIN SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson' AND     firstName = 'Steve';

The output is:

3) Find employees whose last name is Patterson and first name is Steve or Mary:

1 2 3 4 5 6 7 8 9 10

SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson' AND     (firstName = 'Steve' OR     firstName = 'Mary');

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:

1 2 3 4 5 6 7 8 9 10

EXPLAIN SELECT     firstName,     lastName,     email FROM     employees WHERE     lastName = 'Patterson' AND     (firstName = 'Steve' OR     firstName = 'Mary');

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:

1 2 3 4 5 6 7 8

SELECT     firstName,     lastName,     email FROM     employees WHERE     firstName = 'Leslie';

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.

1 2 3 4 5 6 7 8 9

SELECT     firstName,     lastName,     email FROM     employees WHERE     firstName = 'Anthony' OR     lastName = 'Steve';

In this tutorial, you have learned how to use the MySQL composite index to speed up the queries.