78 seconds to 2: the covering index that fixed our slowest page

7.1 relevance

Database covering index optimization story, highly actionable for performance tuning.

General dev.to

Answer the board on AI readiness

Arrive with evidence, not opinion. See how your foundation benchmarks against your peers, get a clear read, and leave with a ranked place to start.

Why It Matters

For platform engineers and backend devs, this is a textbook case of how covering indexes and column ordering solve real-world query performance problems that multiple single-column indexes cannot, especially relevant for SaaS reporting pages that degrade under customer data growth.

Key Takeaways

Design covering indexes by placing equality filter columns first, then GROUP BY columns, then sort columns — and use ALGORITHM=INPLACE for zero-downtime index creation on production tables.

78 seconds to 2: the covering index that fixed our slowest page

Summary

A covering index with six columns in precise order cut a reporting query from 78 seconds to 2 seconds on a MySQL table with ~700k rows. The fix eliminated 20,000 random I/O lookups by including filter columns (moved_to_history, deleted_at) and the GROUP BY column (user_id) in the index, enabling the engine to satisfy the query entirely from the index without touching the clustered table. The index was deployed live using MySQL's INPLACE algorithm to avoid table locking.

Author

Nitish Anand