Treat a MySQL index as a hypothesis

An index can make a query dramatically faster, but every index also consumes storage and must be maintained on inserts, updates, and deletes. The right question is not “which columns deserve indexes?” but “which measured query needs a different access path?”

Start with the query shape

Capture the actual SQL and the parameters used in production-like traffic. Note the filter predicates, join keys, sort order, and pagination. A composite index is ordered: an index on (tenant_id, status, created_at) can support filters beginning with tenant_id, but generally will not replace an index designed for a query that filters only by status.

Inspect the plan

Use EXPLAIN to see candidate keys, estimated rows, access type, and extra operations. On supported MySQL versions, EXPLAIN ANALYZE executes the query and reports actual timing and row counts; use it carefully on expensive statements and representative data. Compare estimates with observed rows—large gaps can signal stale statistics or skewed values.

Consider selectivity and ordering

A boolean column by itself often filters little. It may still help as a later part of a composite key when paired with a tenant or date condition. An index can also satisfy an ORDER BY and avoid a filesort, but only when the key order and direction fit the query.

Measure the trade-off

• Benchmark the query with realistic data volume and parameter distribution.
• Measure insert and update throughput before and after adding the index.
• Check index size and buffer-pool pressure.
• Test whether an existing index already provides the same leading columns.

Do not keep indexes solely because they look plausible. Deploy changes with a rollback plan, observe the target query, and remove redundant indexes only after verifying their use. Query plans are evidence, not promises: validate behavior on the database version and data distribution that matter to the application.