How to Create a MySQL Composite Index on Multiple Columns

A composite index, also known as a multiple-column index, is a database index defined on two or more columns of a table. In MySQL, creating these indexes can significantly optimize query performance, especially for search queries that filter by multiple fields. This guide explains how to create a composite index in MySQL using both the CREATE INDEX and ALTER TABLE statements, along with the critical rule of column ordering to ensure maximum query efficiency.

Creating a Composite Index on an Existing Table

If you already have a table and want to add a composite index, you can use the CREATE INDEX statement or the ALTER TABLE statement.

Using CREATE INDEX is the most common and readable method:

CREATE INDEX index_name 
ON table_name (column1, column2, column3);

Alternatively, you can achieve the same result using the ALTER TABLE statement:

ALTER TABLE table_name 
ADD INDEX index_name (column1, column2, column3);

For example, if you have an employees table and frequently search by both last_name and first_name, you can create a composite index like this:

CREATE INDEX idx_name_composite 
ON employees (last_name, first_name);

Creating a Composite Index During Table Creation

If you are designing a new database, you can define the composite index directly inside your CREATE TABLE statement. To do this, list the index definition at the end of the table structure:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    INDEX idx_name_composite (last_name, first_name)
);

How MySQL Uses Composite Indexes: The Leftmost Prefix Rule

When creating a composite index, the order of the columns is extremely important. MySQL uses composite indexes based on the “Leftmost Prefix Rule.” This means that the index is only useful if your queries filter by the column(s) defined at the beginning of the index.

Consider a composite index defined on (col1, col2, col3):

Because of this behavior, you should always place the most frequently queried columns, and the columns with the highest selectivity (cardinality), first in your index column list.