How to Choose Column Order in a MySQL Composite Index

When a MySQL query filters on several columns, the order of those columns in an index determines how MySQL can use it. A composite index stores values from multiple columns in a set order. MySQL sorts by the first indexed column, then by the next column within each group, and continues in that order.

For example, an index on (account_id, status, created_at) sorts rows first by account_id, then by status within each account, and finally by created_at. MySQL can use the index efficiently when a query filters on the first column or on consecutive columns from the start of the index. Choose an order that fits your query’s filters and sorting needs, then check the plan with EXPLAIN.

Match the index to the query’s leftmost columns

MySQL’s leftmost-prefix rule means an index on (account_id, status, created_at) supports lookups using account_id, (account_id, status), or all three columns. A query filtering only on status, or on status and created_at, does not match a leftmost prefix. The MySQL Reference Manual describes this behavior.

Start with the queries you want to speed up. For example, this query selects recent orders for one account and status:

SELECT order_id, created_at
FROM orders
WHERE account_id = ?
  AND status = ?
  AND created_at >= ?;

An index on (account_id, status, created_at) puts the query’s equality conditions before its date range. An equality condition checks for a specific value, such as account_id = ?. A range condition selects a span of values, such as created_at >= ?. Putting equality columns before range columns is a useful starting point for many filter queries. MySQL index optimization guidance also recommends this order.

If another common query filters by status alone, the example index does not provide a matching leftmost prefix for that lookup. You may need a separate index, or a different leading column, depending on which queries matter most.

Choose among equality columns by reuse and selectivity

When a query has several equality conditions, either column might be a plausible first choice. Consider both how many rows each condition filters out and whether other queries can use the resulting leftmost prefix.

Selectivity measures how much a column narrows a search. A column with many distinct values often has higher selectivity than one with few values. For example, status may be a weak first column on its own if it has few possible values. It can still help after a more selective column in a composite index.

Do not automatically put the most selective column first. If queries often filter by account_id alone, placing account_id first lets them use the index. If queries often filter by another equality column alone, putting that column first could serve more of your workload. Compare the queries you run before deciding. The composite index design discussion also considers selectivity and prefix reuse.

Account for sorting and returned columns

The query’s ORDER BY clause also affects column order. If the filter columns come first and the remaining index columns match the requested sort order, MySQL may use the index to avoid a separate sort. For example, an index on (account_id, status, created_at) may suit a query that filters on account_id and status, then orders by created_at. Check with EXPLAIN, because the full query conditions determine whether MySQL can use the index for sorting.

You can also add columns that the query returns. MySQL can then read those values from the index without fetching the matching table rows. This is called a covering index. In MySQL’s EXPLAIN output, Using index in the Extra column means the query reads only from the index. Extra columns make the index larger, and the database must update each index when rows change. Add returned columns only when the faster reads justify the storage and write costs.

Check the plan and the query’s actual workload

Use EXPLAIN to see whether MySQL chooses your index. Check the key column for the chosen index and review the estimated number of rows examined. If your MySQL setup supports EXPLAIN ANALYZE, it runs the query and reports actual execution details. A table scan does not by itself mean the index order is wrong. Scanning a small table or returning many of its rows can cost less than using an index.

Test the index with representative queries, including those that use only a leftmost prefix. If MySQL ignores the index or examines more rows than expected, compare the plan with one using a different column order. Check whether that order better fits the filters and sorting. Keep indexes that improve important queries. The database uses storage for each additional index and does more work when it writes rows.

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