Choose a MySQL composite index from the query you actually run

A tenant's invoice list filters by tenant, filters by status, and orders by creation time. Adding separate indexes to every column is not automatically the best answer. Start with the full query and representative data.

Write the access pattern down

SELECT id, created_at, total
FROM invoices
WHERE tenant_id = 17
  AND status = 'unpaid'
ORDER BY created_at DESC, id DESC
LIMIT 25;

One candidate to evaluate is an index beginning with tenant_id and status, followed by the ordering columns. That is a hypothesis to test, not a universal recommendation for every invoice table.

Compare plans and runtime

Use EXPLAIN to inspect the access plan. For safe representative reads, EXPLAIN ANALYZE can show actual execution behavior, but it executes the query. Understand the workload before running diagnostics on a busy production database.

Measure with realistic tenant sizes and status distributions. A development table with twenty rows can make almost any index look unnecessary. A single large tenant may behave differently from many small ones.

Count the write cost

Every additional index has storage and maintenance cost. An index created for an unused report can slow down the writes that matter every minute. Review existing indexes before adding another overlapping one.

Test pagination behavior as well. A correct index does not fix an unstable sort order. The ID tie-breaker in this query gives rows sharing a timestamp a deterministic order.

Keep the query, candidate index, representative fixture description, and before/after observations together in the change. That lets the next developer understand why the index exists. MySQL's multiple-column index reference explains leftmost-prefix behavior; EXPLAIN documents plan inspection.