MySQL Composite Indexes: Column Order, the Leftmost Prefix Rule, and When They Stop Helping

The short answer

If your queries filter on more than one column, a single composite B‑Tree index on those columns usually beats two separate single‑column indexes. MySQL can filter across all leading columns of one composite index in a single scan, whereas it typically picks only one of your single‑column indexes and filters the rest row by row. The catch: column order inside the index decides which queries can use it at all, and a range condition on one column disables every column after it.

How a composite index actually works

A composite index on (tenant_id, created_at) stores entries sorted first by tenant_id, then by created_at within each tenant. Think of a phone book: sorted by last name, then first name. You can find everyone named "Smith" quickly, or "Smith, John" quickly — but you cannot find all the "John"s quickly, because first names are only sorted within each last name.

This is the leftmost prefix rule. An index on (a, b, c) supports lookups on:

• a
• a, b
• a, b, c

It does not efficiently support a lookup on b alone or c alone. MySQL 8.0 can sometimes fall back to an index skip scan for a missing leading column, but that is a workaround for low‑cardinality leading columns, not a substitute for correct ordering.

A worked example

Suppose an orders table where the hot query is "recent orders for one tenant with a status filter":

CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  total DECIMAL(10,2) NOT NULL
);

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

A good index for this query is:

ALTER TABLE orders
  ADD INDEX idx_tenant_status_created (tenant_id, status, created_at);

Run this in any MySQL client with ALTER privilege on the table. On a large table, ALTER TABLE ... ADD INDEX rebuilds data; on MySQL 8.0 it normally uses the online INPLACE algorithm, but expect extra I/O and consider running it during low traffic.

Why this order? Equality columns come first (tenant_id, then status), and the range/sort column (created_at) comes last. Because the first two columns are matched with equality, the entries for that tenant/status pair are contiguous and sorted by created_at, so MySQL can also satisfy the ORDER BY from the index without a filesort.

Verify it with EXPLAIN

Run EXPLAIN (no special privileges beyond SELECT) in front of the query and check three fields:

• key should show idx_tenant_status_created. If it is NULL, the index is not being used.
• rows is the estimated rows examined; it should drop dramatically versus the unindexed plan.
• Extra: Using index means a covering index (all selected columns are in the index, so no row lookup). Using filesort here would mean the sort is not satisfied by the index.

Note that rows is an optimizer estimate, not a measured count — treat it as directional. For a real comparison, time the query with SET profiling = 1; plus SHOW PROFILES;, or use EXPLAIN ANALYZE on MySQL 8.0.18+, which shows actual execution times (and actually runs the query, so avoid it on expensive writes).

The range‑condition trap

The most common mistake: putting the range column in the middle. An index on (tenant_id, created_at, status) can use tenant_id for equality and created_at for the range, but status after the range cannot be used for the lookup portion of the scan — MySQL reads every matching row in the date range and filters status afterward. Rule of thumb: equality columns first, then at most one range column, placed last.

Limits and common mistakes

• Redundant indexes. If you have (tenant_id, status, created_at), a separate index on (tenant_id) is redundant — the composite index already serves tenant‑only lookups. Drop the shorter one to save write cost.
• Over‑indexing. Every index is maintained on each INSERT, UPDATE, and DELETE. Five indexes on a write‑heavy table can matter more than the read gains.
• Cardinality folklore. The old advice "highest cardinality first" is secondary to the equality‑before‑range rule. For equality‑only queries, order among the equality columns rarely changes performance much; put the range column last regardless of its cardinality.
• Functions on indexed columns. WHERE DATE(created_at) = '2026-10-01' defeats the index on created_at. Rewrite as a range: created_at >= '2026-10-01' AND created_at < '2026-10-02'.
• Low‑selectivity leading columns. An index leading with a boolean‑like column (two distinct values) is usually a poor choice unless combined with more selective columns behind it.

Practical checklist

1. Identify the exact WHERE, ORDER BY, and join columns of the slow query.
2. Build one composite index: equality columns first, range/sort column last.
3. Confirm with EXPLAIN that key matches and rows dropped; check Extra for Using index or unwanted Using filesort.
4. Remove any single‑column index now redundant as a leftmost prefix.
5. Re‑check write latency on the table if it is insert‑heavy.