Composite and Covering Indexes in MySQL: Getting Column Order Right

Most slow queries I get asked to look at are not slow because of the query. They are slow because of the index, or the lack of one. And the single most common mistake is not the absence of an index but the wrong column order in a composite one. MySQL will happily let you create an index that looks reasonable and never gets used. Understanding why comes down to one rule and a couple of habits worth building.

A Composite Index Is One Sorted List

A composite index, sometimes called a multi-column index, is a single B-tree built over two or more columns. The thing to internalize is that it is sorted by the first column, then by the second within each value of the first, then by the third within each value of the second, and so on. It behaves exactly like a phone book sorted by last name, then first name. You can find everyone named “Smith” instantly, and “Smith, John” just as fast, but the book is useless if all you know is the first name “John.”

That phone book analogy is the whole leftmost prefix rule in one sentence. Given an index on (a, b, c), MySQL can use it for queries that filter on a, on a and b, or on a, b, and c. It cannot use it efficiently for a query that filters only on b, or only on c, or on b and c together, because those columns are not the starting point of the sort.

Here is a concrete table. Say we run an events platform like PHP Tek and store ticket sales:

CREATE TABLE ticket_sales (
    id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    event_id     INT UNSIGNED NOT NULL,
    status       VARCHAR(20) NOT NULL,
    purchased_at DATETIME NOT NULL,
    amount_cents INT UNSIGNED NOT NULL
);

CREATE INDEX idx_event_status_date
    ON ticket_sales (event_id, status, purchased_at);

This index serves a query filtering on event_id alone, on event_id and status, and on all three columns. It does nothing for a query that filters only on status.

Order by Equality First, Range Last

The rule that trips up even experienced developers is what happens when a range condition enters the picture. Once MySQL hits a column used with a range operator such as >, <, BETWEEN, or LIKE 'prefix%', it stops using subsequent index columns for further lookups. The range column is where the useful part of the index ends.

This is why the established guidance is to put equality-filtered columns first and range-filtered columns last. Consider:

SELECT amount_cents
FROM ticket_sales
WHERE event_id = 42
  AND status = 'paid'
  AND purchased_at >= '2026-06-01';

With (event_id, status, purchased_at), MySQL narrows by the two equality conditions, then does an efficient range scan on purchased_at. Perfect. But if you had built the index as (purchased_at, event_id, status), the leading range on the date would force MySQL to stop there, and the event_id and status columns in the index could not help filter. Same columns, completely different outcome.

A reasonable tiebreaker among equality columns is cardinality: putting higher-cardinality (more distinct values) columns earlier tends to narrow results faster. But correctness of query patterns always comes before cardinality. Index the way your queries actually filter.

Covering Indexes: Skip the Table Entirely

A covering index is a composite index that happens to contain every column a query needs, both for filtering and for the columns it returns. When this is true, MySQL answers the query straight from the index B-tree and never touches the table rows. Because InnoDB stores table data in the clustered primary-key index, avoiding that secondary lookup can be a meaningful win on hot queries.

You can spot a covering index in action by running EXPLAIN and looking for Using index in the Extra column. That phrase specifically means the read was satisfied by the index alone. Do not confuse it with Using index condition, which is index condition pushdown, a related but different optimization where the table still gets read.

EXPLAIN
SELECT event_id, status, purchased_at
FROM ticket_sales
WHERE event_id = 42 AND status = 'paid';

Because all three selected columns live in idx_event_status_date, the Extra column reports Using index. Add amount_cents to the SELECT and the covering property is lost, since that column is not in the index, so MySQL must visit the rows. If that query is hot enough, you might extend the index to include amount_cents as a trailing column purely to keep it covering. The trade-off is real: wider indexes cost more storage and slow writes, so reserve covering indexes for queries that genuinely matter.

Read EXPLAIN Like a Checklist

When I evaluate a query, I scan the EXPLAIN output for a few things. The type column tells you the access method: ref and eq_ref mean MySQL is seeking through an index, range is a bounded scan, while index (a full index scan) and ALL (a full table scan) are the ones to be suspicious of. The key column confirms which index was actually chosen, which is not always the one you expected. And the Extra column flags trouble such as Using filesort or Using temporary, both signs that sorting or grouping could not lean on the index order.

One modern nuance worth knowing: since MySQL 8.0.13 the optimizer has an index skip scan that can, in narrow cases, use a composite index even when the leading column is absent from the WHERE clause, provided that leading column has few distinct values. It is a genuine optimization but a fragile one. Treat it as a safety net, not a design strategy. Build your indexes for the leftmost prefix rule and let skip scan be a bonus.

The Practical Takeaway

You rarely need one index per column. A well-ordered composite index frequently replaces several single-column indexes and serves a whole family of queries. Start from the queries you actually run, list the columns by how they are used (equality first, then one range, then anything needed only to make the index covering), and verify with EXPLAIN that the key and Extra columns say what you expect. Do that consistently and most of your “slow query” tickets quietly disappear.

A Composite Index Is One Sorted List

Order by Equality First, Range Last

Covering Indexes: Skip the Table Entirely

Read EXPLAIN Like a Checklist

The Practical Takeaway

Sources