MySQL Composite Index Column Order for Filtered and Sorted Queries

In a MySQL composite index, put columns used in equality conditions first, then the column used for sorting or range filtering. The index is usable from its leftmost column onward, so column order decides which queries it can serve. A well-ordered index can both filter rows and return them already sorted, avoiding an expensive filesort.

What Is the Leftmost Prefix Rule?

A composite index is sorted by its first column, then by the second within equal first values, and so on. Because of this structure, MySQL can use the index efficiently only for conditions that start from the leftmost column and continue without gaps.

Consider an index on (customer_id, status, created_at). It can serve queries filtering on customer_id alone, on customer_id and status, or on all three. It cannot efficiently serve a query that filters only on status or only on created_at, because those columns are not at the beginning of the index.

CREATE INDEX idx_orders_cust_status_created
  ON orders (customer_id, status, created_at);

This is why the same three columns in a different order create a different index with different capabilities. There is no single correct order in the abstract, only one that fits your actual queries.

How Should You Order Columns for Equality, Range, and Sort?

A practical guideline is to place equality columns first, then the column used for ordering, and put range conditions after. Once MySQL reaches a column used in a range condition, columns after it in the index generally cannot be used for further narrowing or for ordering.

SELECT id, total
FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

The index (customer_id, status, created_at) fits this query well. MySQL jumps directly to the rows for the customer and status, and reads them in created_at order, so the LIMIT can stop early. Without the right order, MySQL would collect all matching rows and sort them separately.

• Equality columns first, since they narrow the search to a contiguous range of the index.
• The ORDER BY column next, so rows come out already sorted.
• Range columns last, because they end the usable prefix for later columns.

Does Column Selectivity Matter for Ordering?

You may have heard that the most selective column should come first. That advice is incomplete. Selectivity matters, but query patterns matter more. A highly selective column that is rarely filtered on alone is a poor first column if most queries start with another one.

Consider which columns appear in the most important and most frequent queries, and design around them. If several queries share a leading column, a single composite index may serve them all. If they differ, you may need more than one index, accepting the extra write cost.

Also remember that low-cardinality columns, such as a status flag, can still be valuable in a composite index when combined with other columns. They are not useful alone, but as part of a prefix they narrow the range further.

How Do You Verify an Index Is Used?

Use EXPLAIN to see the chosen index, the key length used, and whether the plan includes a filesort. The key_len value shows how many bytes of the index were used, which hints at how many columns participated. The Extra column can show Using filesort or Using index.

EXPLAIN SELECT id, total FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

Test with realistic data volumes, because the optimizer's choices depend on table statistics. Output details and available features, such as descending index support, differ by MySQL version, so check the official documentation for yours.

When a query is slow in production, compare its plan there with the plan on your development database. Differences in data distribution and statistics can lead the optimizer to choose a different index, so running ANALYZE TABLE and testing against a recent copy of production data often explains surprising results.

Frequently Asked Questions

Can MySQL use part of a composite index?

Yes, but only a leftmost prefix. Conditions on later columns without the earlier ones typically cannot use the index efficiently.

Should I create separate indexes for each column instead?

Usually a composite index serves multi-condition queries better. MySQL can sometimes merge separate indexes, but that is often slower than one well-designed composite index.

Does the order of conditions in the WHERE clause matter?

No. The optimizer reorders them. What matters is the order of columns in the index definition.

How many columns should a composite index have?

As few as needed. Wide indexes cost more to store and update, so add columns only when they help real queries.

Summary

Order composite index columns by how queries use them: equality first, then sort, then range. Respect the leftmost prefix rule, verify with EXPLAIN, and check the MySQL documentation for version-specific optimizer behavior.