Stop Guessing Your Index Order: Mastering MySQL Composite Indexes

The Cost of a Misplaced Column

You have a table with millions of rows and a composite index on (last_name, first_name). Your query filters by first_name, and suddenly, MySQL ignores your index entirely, opting for a full table scan. This happens because MySQL indexes are not bags of columns; they are sorted hierarchies. If you don't follow the Leftmost Prefix Rule, your index is essentially invisible to the optimizer.

The goal of a composite index is to narrow down the search space as quickly as possible. When designed correctly, these indexes allow MySQL to jump directly to a small slice of data, avoiding expensive disk I/O and temporary tables.

The Leftmost Prefix Rule

A composite index is stored as a sorted list. If you create an index on (A, B, C), MySQL sorts the data by A, then by B within each A, then by C within each B. Because of this structure, the index can only be used if the query filters by the leftmost column (A), or the leftmost two (A and B).

• Query on A: Index used.
• Query on A and B: Index used.
• Query on B and C: Index ignored (or poorly used).

If you frequently query by column B alone, you cannot rely on an index that starts with A. You would need a separate index starting with B.

Selectivity and Range Constraints

Not all columns are created equal. Selectivity refers to the ratio of unique values to the total number of records. A column like user_id has high selectivity, while gender or status has low selectivity.

Generally, placing the most selective column first allows MySQL to discard the largest amount of irrelevant data immediately. However, there is a critical caveat: Range conditions. If your query uses a range operator (>, <, BETWEEN) on a column, MySQL can use the index for that column, but it cannot use any subsequent columns in the composite index for filtering. To maximize efficiency, place equality filters first and range filters last in your index definition.

Worked Example: Optimizing an Order Search

Imagine an orders table with columns store_id, order_date, and customer_id. We want to optimize this query:

SELECT order_id, total_amount FROM orders WHERE store_id = 5 AND order_date > '2023-01-01';

Wrong Approach: (order_date, store_id)

If the index is (order_date, store_id), MySQL hits the range condition on order_date first. It can find all orders after January 1st, but it must then manually scan those results to find those belonging to store_id = 5.

Correct Approach: (store_id, order_date)

By placing the equality filter first, MySQL jumps straight to store_id = 5 and then performs a narrow range scan for the dates. This is significantly faster.

Implementation and Verification

Run this on your MySQL instance (requires INDEX permissions on the table):

CREATE INDEX idx_store_date ON orders (store_id, order_date);

To verify the index is being used as intended, run the EXPLAIN command:

EXPLAIN SELECT order_id, total_amount FROM orders WHERE store_id = 5 AND order_date > '2023-01-01';

Check the output: Look at the key column to ensure idx_store_date is selected. Check the rows column; a low number relative to the table size indicates the index is effectively pruning the search space.

The Trade-off: Write Overhead and Memory

Composite indexes are not free. Every time you INSERT, UPDATE, or DELETE a row, MySQL must update every index associated with that table. Large composite indexes increase disk I/O and consume more space in the InnoDB Buffer Pool (the memory area where MySQL caches data and indexes).

Additionally, beware of Covering Indexes. If your SELECT clause only requests columns that are already part of the composite index, MySQL will return the data directly from the index without ever touching the actual table (the clustered index). While this is incredibly fast, adding extra columns to an index just to make it "covering" can bloat the index and slow down writes.

Actionable Summary

Before adding a composite index, map your most frequent queries. Order your columns from most selective to least selective, ensuring that columns used for equality checks come before those used for ranges. Always verify the result with EXPLAIN to ensure the optimizer isn't ignoring your hard work.