MysqlHow-ToBeginner · 3 min read

How to Create Composite Index in MySQL: Syntax and Examples

In MySQL, you create a composite index by listing multiple columns inside CREATE INDEX or ALTER TABLE statements separated by commas. This index helps speed up queries that filter or sort by those columns together.

📐

Syntax

The basic syntax to create a composite index in MySQL is:

CREATE INDEX index_name ON table_name (column1, column2, ...); creates an index on multiple columns.
ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...); adds a composite index to an existing table.

The order of columns matters because MySQL uses the index from left to right.

sql

CREATE INDEX idx_name ON table_name (column1, column2);

-- Or using ALTER TABLE
ALTER TABLE table_name ADD INDEX idx_name (column1, column2);

💻

Example

This example creates a composite index on the last_name and first_name columns of the employees table. It helps speed up queries filtering by both columns.

sql

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

-- Create composite index on last_name and first_name
CREATE INDEX idx_name ON employees (last_name, first_name);

-- Sample query that benefits from this index
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';

Output

Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.02 sec) -- The SELECT query returns matching rows quickly using the composite index.

⚠️

Common Pitfalls

Common mistakes when creating composite indexes include:

Creating the index with columns in the wrong order, which can reduce its effectiveness.
Expecting the index to speed up queries filtering only by the second or later columns.
Adding unnecessary composite indexes that duplicate existing single-column indexes.

Remember, MySQL uses the index starting from the first column, so queries must filter on the leftmost columns to benefit.

sql

/* Wrong order example: */
CREATE INDEX idx_wrong ON employees (first_name, last_name);

/* Correct order example: */
CREATE INDEX idx_correct ON employees (last_name, first_name);

📊

Quick Reference

Term	Description
Composite Index	An index on multiple columns to speed up queries using those columns together.
CREATE INDEX	SQL command to create an index on a table.
ALTER TABLE ADD INDEX	Alternative command to add an index to an existing table.
Column Order	The sequence of columns in the index affects query optimization.
Leftmost Prefix	MySQL uses the index starting from the first column in the list.

✅

Key Takeaways

Create composite indexes by listing multiple columns in CREATE INDEX or ALTER TABLE statements.

The order of columns in the index matters for query performance.

Composite indexes speed up queries filtering on the leftmost columns in the index.

Avoid redundant indexes and ensure the index matches your query patterns.

Use EXPLAIN to check if your queries use the composite index effectively.