Database
September 22, 2026
2 min read
MySQL Indexes for Reporting Queries
Use execution plans and real workloads to choose composite indexes, write searchable date predicates, and measure read/write trade-offs.
- MySQL
- Indexes
- Performance
Index the Query You Actually Run
Reporting queries often combine a date range, tenant or account scope, status filters, and a grouping operation. Adding an index to every filtered column independently may not help the important query. First capture the SQL the application emits and inspect its execution plan with representative data. The optimizer's estimate and chosen access path are more useful than intuition alone.
A composite index can serve queries that use its leftmost columns in order. If a common report filters by account_id and created_at, an index beginning with those columns may help that access pattern; reversing the order can behave differently for queries that only filter by date. Include status only when it materially narrows the scan or supports another frequent access path. Validate the choice with the actual workload.
Keep Predicates Searchable
Applying a function to an indexed column can make a predicate harder to optimize. Instead of wrapping a timestamp in a date-extraction function, consider a half-open range: greater than or equal to the period start and less than the next period start. This also avoids ambiguity around fractional seconds and makes adjacent reporting windows easier to reason about.
Be deliberate about covering indexes. Including every selected field can reduce table lookups for a narrow query, but wide indexes consume storage and add work to inserts and updates. A report that scans a large fraction of a table may be faster with a scan than with an index, so compare execution time and rows examined rather than treating index usage as a goal by itself.
Measure the Trade-Off
Run plans against data with realistic distribution, then benchmark the report under normal and peak conditions. Check whether the query uses the intended key, how many rows it examines, whether temporary tables or sorting dominate, and what happens when statistics change. Repeat after adding an index because the optimizer may choose a different plan for related queries.
Finally, manage index changes as production changes. Build and deploy using the database version's supported online DDL behavior, monitor write latency and disk use, and remove redundant indexes only after checking all consumers. Indexes are part of a workload contract: each one should make an important read faster enough to justify the cost it imposes on every write.