Adding hardware hides a slow query; adding the right index removes it. Understanding how MySQL chooses indexes is one of the highest-return skills for anyone maintaining a database-backed application.
Read the execution plan first
EXPLAIN shows which index the optimiser chose and how many rows it expects to examine. A query scanning hundreds of thousands of rows to return ten is telling you exactly what to fix. Never add an index without reading the plan before and after.
Composite index order matters
A composite index on (status, created_at) serves queries filtering on status, and on status with created_at, but not created_at alone. Order columns by selectivity and by how they are queried, with equality conditions before ranges.
Common mistakes
- Wrapping an indexed column in a function, which prevents its use
- Indexing every column individually and hoping for the best
- Leaving redundant indexes that slow every write
- Ignoring the cost of indexes on write-heavy tables
Covering indexes
When an index contains every column a query needs, MySQL answers from the index without touching the table. For hot read paths this can be the difference between milliseconds and seconds.
Keep it maintainable
Review indexes when query patterns change. An index added for a report that no longer runs is pure write overhead.
Every index speeds up reads and slows down writes. Adding one should be a deliberate trade, not a reflex.
Where to start
Enable the slow query log for a week, take the top five queries by total time, and read their execution plans. That list is your work queue.
