Master MySQL Composite Indexes: Unlock the Leftmost Prefix Principle

What Is a Composite Index?

A composite (multi‑column) index in MySQL groups several columns into a single index to accelerate queries that filter on those columns together.

CREATE INDEX idx_users_name_age ON users (name, age);

Frequent multi‑column queries : avoids scanning each column separately.

Reduces index count : a single composite index can replace multiple single‑column indexes, e.g., (col1, col2) instead of separate col1 and col2 indexes.

Index covering : if all required fields are in the composite index, MySQL can return results directly from the index without accessing the table rows.

Creation Order and B+ Tree Structure

In InnoDB a composite index is stored as a B+ tree. The tree is built using the leftmost column first, then the next column, and so on. Sorting priority follows the column order from left to right.

Leftmost Prefix Principle

MySQL scans an index from left to right. It can use the index only if the query references a leftmost prefix of the index columns. A query may use only part of the index, but only the leftmost part.

Full match: WHERE col1 = A AND col2 = B AND col3 = C Partial prefix: WHERE col1 = A AND col2 = B or WHERE col1 = A Skipping columns is not allowed: WHERE col2 = B AND col3 = C cannot use the index.

Key Metrics: Cardinality and Selectivity

Two metrics influence index effectiveness:

Cardinality : number of distinct values in a column.

Selectivity : proportion of distinct values, calculated as COUNT(DISTINCT column) / COUNT(*).

SELECT
  COUNT(DISTINCT first_name) AS first_name_cardinality,
  COUNT(DISTINCT state) AS state_cardinality,
  COUNT(DISTINCT first_name) / COUNT(*) AS first_name_selectivity,
  COUNT(DISTINCT state) / COUNT(*) AS state_selectivity
FROM people;

Result example: first_name_cardinality = 3009, state_cardinality = 2. The column with higher selectivity (e.g., first_name) should be placed earlier in the index.

Range Queries and Their Impact

If a range condition ( >, <, BETWEEN, LIKE) is applied to a column, MySQL can use the index for that column but cannot continue to use it for columns to its right.

SELECT * FROM table WHERE col1 = 100 AND col2 > 200 AND col3 = 300;

Here col1 is used precisely, col2 triggers a range scan, and col3 cannot benefit from the index.

When all selected columns are contained in the composite index, the query can still be a covering index even with a range condition:

SELECT col1, col2 FROM table WHERE col1 = 100 AND col2 > 200;

Scenarios Where a Composite Index Becomes Ineffective

1. Skipping the leftmost column – queries that start with a non‑leftmost column cannot use the index.

2. Range condition on an earlier column – after a range scan, subsequent columns are not used for index lookups.

Example with a LIKE prefix match:

SELECT * FROM table WHERE col1 LIKE 'abc%' AND col2 = 200 AND col3 = 300;

If the LIKE pattern is a left‑anchored prefix (e.g., 'abc%'), the index can still be used for col1, but the effectiveness for col2 and col3 is limited.

Practical Guidelines

Place high‑selectivity columns first in the index.

Avoid skipping leftmost columns in query predicates.

Be aware that range conditions on a column stop index usage for columns to its right, though the index may still serve as a covering index if all required columns are included.

Use composite indexes to replace multiple single‑column indexes when queries frequently involve the same column set.

Conclusion

Composite indexes are powerful for improving MySQL query performance, but their effectiveness depends on proper column ordering, high selectivity of leading columns, and strict adherence to the leftmost‑prefix rule. Range conditions and skipped columns can render parts of the index unusable, so careful design is essential.