How to Choose Column Order in a MySQL Composite Index

When you add a composite index to a MySQL table, the order of its columns determines which queries can use it efficiently. A composite index is one index built from multiple columns, such as (customer_id, status, created_at). MySQL stores those column values in the specified order, so choosing the order starts with understanding how your queries filter and sort rows.

A useful starting rule is to put columns the query checks for one exact value before a column it checks against a span of values, such as a date interval. Then consider the sort order and which queries need to use a leftmost prefix. A leftmost prefix is the first column or consecutive first columns of an index. For example, an index on (a, b, c) supports lookups that start with a, but it does not provide the same lookup path for b alone. The MySQL manual’s explanation of multiple-column indexes describes this behavior.

Put equality conditions before range conditions

Start with the conditions your query uses. If a query matches specific values for some columns and then searches a range on another, place the equality columns first and the range column after them.

A query that filters by customer and status, then requests recent records, could use an index such as:

(customer_id, status, created_at)

The query’s equality conditions narrow the index to entries for that customer and status. MySQL can then scan the relevant portion by created_at. With a range condition such as created_at >= ?, MySQL scans the index entries that match the range. Columns after the range generally cannot narrow that scan further, though they can still help with other parts of the query. Do not assume they reduce the number of index entries MySQL scans. This is why equality-before-range guidance is a useful default.

The same principle applies when a query uses more than one equality condition. If it filters on both customer_id and status, either order can support that query when both conditions are present. The order matters for other queries, though: (customer_id, status) supports a lookup starting with customer_id; (status, customer_id) supports one starting with status. Choose the order that also serves the important leftmost-prefix patterns in your workload.

Choose equality-column order from query patterns

Among columns filtered by equality, selectivity is a secondary consideration. Selectivity describes how well a column narrows the rows. A column with many distinct values often narrows a lookup more than one with only a few. But the most selective column does not automatically belong first. An index must match the query patterns you need to serve, including queries that filter on only a prefix.

Suppose frequent queries filter by customer_id and status, while some also filter by customer_id alone. An index beginning with customer_id supports both patterns. Reversing the columns would support queries beginning with status, but MySQL would not use the index in the same way for queries on customer_id alone. When every indexed column has an equality condition, either column order can serve that fully specified lookup; the choice between orders should account for other queries and their prefixes.

For this reason, do not apply “most selective first” as a universal rule. Compare the conditions in real queries and count how many useful leftmost prefixes each column order supports. The discussion of selectivity and composite-index order also reflects why the answer depends on the workload.

Put sort columns after the equality prefix

If a query filters by equality and then sorts by another column, placing the sort column after the equality columns can let MySQL read matching rows in the requested order from the index. For example, an index on (customer_id, created_at) fits a query that selects one customer’s records ordered by created_at.

The requested sort order must match the order MySQL can read from the index. Other conditions can affect whether MySQL avoids an extra sort. A range condition before a sort column is one reason to verify the result rather than assume the index satisfies ORDER BY. Use EXPLAIN to inspect the plan and check whether MySQL reports Using filesort, which indicates that it performs an additional sort. The MySQL composite-index guidance discusses placing sort columns after equality columns.

Check the index against actual queries

Build the index around the queries that matter, not around a table’s columns in isolation. Review each query’s conditions in WHERE and JOIN ON and its ORDER BY, then compare candidate column orders with EXPLAIN, which shows how MySQL plans to access rows. Check which key, or index, MySQL chooses, how many rows it estimates it will read, and whether the plan uses an extra sort. MySQL’s optimizer chooses a plan by estimating the work each option requires. It may prefer another index even when a composite index appears to fit the query.

Also consider index size and write cost. In InnoDB, each secondary index stores the primary key value alongside its indexed columns, so a wider primary key increases the size of secondary indexes. A covering index contains all columns a query needs, allowing InnoDB to answer it from the secondary index without looking up the full row. Add columns so the index contains everything an important query needs only when that benefit justifies the extra storage and maintenance.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com