MySQL Composite Index Optimization Guide

A query that returns in 8 milliseconds on your laptop can take 4 seconds in production once a table crosses a few million rows. The usual culprit isn't bad hardware or a missing cache layer — it's an index that was never designed for the query actually running against it. MySQL composite index optimization is one of the highest-leverage things a backend developer can learn, because a single well-ordered index often turns a full table scan into a handful of row lookups without touching a line of application code.

This guide walks through how composite indexes actually work under the hood, how to design column order correctly, and how to verify the fix with EXPLAIN instead of guessing.

What a composite index actually does

A composite (or "compound") index is a single B-tree index built across multiple columns, stored in the order you declare them. MySQL can use a composite index for any leftmost prefix of its columns, but not for columns in the middle or at the end unless the ones before them are also part of the query's filter.

That leftmost-prefix rule is the single most misunderstood part of index design. An index on (status, created_at, user_id) can serve:

Queries filtering on status alone
Queries filtering on status and created_at
Queries filtering on all three columns

But it cannot efficiently serve a query that filters only on created_at or only on user_id — MySQL has no way to jump into the middle of the tree without scanning the prefix first.

Why column order matters more than which columns you pick

Column order determines selectivity at each level of the tree. As a rule of thumb:

Put equality-filtered columns first (WHERE status = 'active')
Put range-filtered columns after that (WHERE created_at > ?)
Put columns used only for sorting or covering last

Once MySQL hits a range condition in a composite index, it stops using the remaining columns for further filtering — it can still use them for a covering index, but not for narrowing the row set. This is why an index built as (created_at, status) often performs far worse than (status, created_at) for the exact same query: the range column eats the rest of the index's usefulness if it comes first.

Step-by-step: designing an index for a real query

Take this common pattern from an orders table:

SELECT id, total, created_at
FROM orders
WHERE status = 'pending'
  AND created_at >= '2026-09-01'
ORDER BY created_at DESC
LIMIT 50;

Without the right index, MySQL scans every pending-eligible row, sorts the result set in memory or on disk, and only then applies the limit. Here's the fix:

CREATE INDEX idx_orders_status_created
ON orders (status, created_at);

Because status is filtered by equality and created_at is filtered by range and used for ordering, this order lets MySQL:

Seek directly to the pending rows in the B-tree
Walk them in created_at order (already sorted, no filesort needed)
Stop after 50 rows instead of scanning the whole range

Verify it actually worked instead of assuming:

EXPLAIN
SELECT id, total, created_at
FROM orders
WHERE status = 'pending'
  AND created_at >= '2026-09-01'
ORDER BY created_at DESC
LIMIT 50;

Look for key: idx_orders_status_created, a low rows estimate relative to table size, and no Using filesort in the Extra column. If Using filesort still shows up, the index order doesn't match the sort direction or an extra column is breaking the sequence.

Covering indexes: the next level

If you go a step further and include every column the query actually selects, MySQL can answer the query entirely from the index without touching the table's data pages at all — this is called a covering index:

CREATE INDEX idx_orders_covering
ON orders (status, created_at, id, total);

EXPLAIN will show Using index in Extra when this happens. Covering indexes are especially valuable for high-traffic read endpoints and paginated list views, where the same query shape runs thousands of times per hour.

Common mistakes that quietly defeat an index

Wrapping the indexed column in a function, like WHERE DATE(created_at) = '2026-09-01' — this prevents MySQL from using the index at all, since it has to evaluate the function per row. Rewrite as a range: created_at >= '2026-09-01' AND created_at < '2026-09-02'.
Mismatched column types, such as comparing a VARCHAR column to an integer literal — MySQL silently converts types and can skip the index.
Too many indexes on one table — every index adds write overhead, since MySQL updates all of them on every INSERT/UPDATE/DELETE. Audit with SHOW INDEX FROM orders periodically and drop ones that no query plan actually uses.
Ignoring cardinality — an index on a boolean-like column with only two distinct values rarely helps on its own; it needs a more selective column ahead of it in the composite order.
Adding ORDER BY columns after a range filter in the index definition, which breaks the sort-avoidance benefit described above.

Best practices for ongoing index maintenance

Review slow query logs weekly, not just when someone complains
Use EXPLAIN ANALYZE (MySQL 8.0.18+) to see actual execution time per step, not just the estimated plan
Keep composite indexes aligned with your most frequent query shapes, not every theoretical filter combination
Re-evaluate index usefulness after major schema or traffic pattern changes — an index that helped at 100K rows can behave differently at 50M

Frequently Asked Questions

Does column order in a composite index matter for WHERE clauses using AND? Yes. MySQL reads the index left to right, so the leftmost columns must match your equality filters for the rest of the index to be usable. Swapping the order of columns in the CREATE INDEX statement can turn a full index scan into a targeted seek, even though the WHERE clause itself never changes.

Can one composite index replace several single-column indexes? Often yes, for queries that filter on the same combination of columns together, and it's usually faster to maintain than several separate indexes. But if different queries filter on different single columns independently, you may still need standalone indexes for those specific patterns.

How many columns should a composite index have? There's no fixed number, but past 3–4 columns the index gets wide, slower to update, and harder to reason about. Prioritize the columns that appear together in your actual WHERE and ORDER BY clauses rather than adding every filterable field.

Will adding an index ever make a query slower? The read query itself won't get slower, but every write to the table now has to update that index too, and the query planner has more options to evaluate, which occasionally picks the wrong index. Keep the total number of indexes per table proportional to how often it's actually queried versus written to.

Key Takeaways

Composite index design comes down to three checks: put equality columns before range columns, confirm the plan with EXPLAIN instead of assuming it worked, and periodically audit unused indexes since every one of them costs write performance. Run EXPLAIN on your slowest production query today, check whether Using filesort or Using temporary shows up in the Extra column, and if it does, rebuild the index with the filter and sort columns in the correct leftmost order.