MySQL Composite Indexes: Start With the Query Shape

A composite index is an ordered structure, not a bag of indexed columns. Choosing its column order means choosing which subsets of a query can narrow the scan and whether the remaining order matches the requested result. There is no universally correct “most selective column first” rule.

Start with a concrete equality predicate, range predicate, ordering and limit. Then test the candidate access path against representative data rather than guessing from the number of columns in the WHERE clause.

The concrete problem

Consider a customer-support application that displays the most recent open tickets for one tenant. The useful access pattern fixes tenant_id and status, then reads created_at in descending order with a deterministic identifier tie-breaker. The example targets MySQL 8.4 with InnoDB.

Figure: Tenant equality → Status equality → Ordered recent tickets.

A worked example

CREATE TABLE tickets (
  id BIGINT PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  subject VARCHAR(200) NOT NULL,
  INDEX tenant_status_recent
    (tenant_id, status, created_at DESC, id DESC)
);
EXPLAIN FORMAT=TREE
SELECT id, created_at, subject FROM tickets
WHERE tenant_id = 42 AND status = 'open'
ORDER BY created_at DESC, id DESC LIMIT 50;

Why the behavior matters

Equality conditions on the leading columns identify a contiguous part of the index. Within that part, the timestamp and identifier order match the requested presentation. The subject is not in this index, so retrieving it still requires row access. That can be acceptable when the limit is small; making every index cover every selected column increases storage and write work.

Change the query to omit status and the same index may no longer provide the desired global timestamp order for that tenant. Change the query to filter on a created_at range and additional columns have a different role in narrowing versus filtering. “Uses an index” is therefore not a sufficient performance conclusion.

Failure modes and design tradeoffs

InnoDB secondary indexes include the primary-key value, but that implementation detail does not automatically make arbitrary ORDER BY directions or covering assumptions correct. Inspect the plan. Low-cardinality status values can still be useful after a tenant equality predicate; rejecting them because they are individually unselective ignores the query shape.

Every extra index consumes disk, cache and maintenance work on inserts and updates. Keep competing candidates only long enough to evaluate them, and consider write workload as well as the fast read demonstration.

What to verify before adoption

Seed both large and small tenants, realistic status skew and timestamp ties. Compare examined rows, sort behavior and elapsed time under controlled cache conditions. Run EXPLAIN ANALYZE only when executing the query is safe. Recheck pagination correctness with ties and test the second page, not only LIMIT 50 from the beginning.

Primary reference

• MySQL documentation. Verify the exact deployed version and driver behavior before using the example in a production runbook.