Composite indexes in MySQL

Introduction

Composite indexes help speed up searches when you look for data using more than one column together.

When you often search for records using two or more columns at the same time.

When you want to sort or filter data based on multiple columns.

When you want to make queries faster that use conditions on several columns.

When you want to avoid scanning the whole table for combined column searches.

Syntax

MySQL

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

The order of columns in the index matters; it should match how you query the data.

Composite indexes work best when queries use the leftmost columns first.

Examples

Creates a composite index on the 'name' and 'age' columns of the 'users' table.

MySQL

CREATE INDEX idx_name_age ON users (name, age);

Creates a composite index on 'city' and 'state' columns to speed up searches filtering by both.

MySQL

CREATE INDEX idx_city_state ON customers (city, state);

Sample Program

This example creates a table and a composite index on last_name and department. The EXPLAIN shows how MySQL uses the index for the query.

MySQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50)
);

CREATE INDEX idx_name_dept ON employees (last_name, department);

INSERT INTO employees (id, first_name, last_name, department) VALUES
(1, 'Alice', 'Smith', 'Sales'),
(2, 'Bob', 'Johnson', 'HR'),
(3, 'Carol', 'Smith', 'HR');

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' AND department = 'HR';

Important Notes

Composite indexes speed up queries that filter on the first column or the first and second columns together.

If you query only the second column without the first, the composite index may not be used.

Keep indexes small and only on columns you query often to avoid slowing down inserts and updates.

Summary

Composite indexes use multiple columns to speed up searches involving those columns together.

The order of columns in the index is important for performance.

Use composite indexes when your queries filter or sort by multiple columns at once.