Database Indexes Made Simple: How I Spot and Fix Bottlenecks in MySQL
When tables grow from a few hundred rows to hundreds of thousands, queries that used to feel instant suddenly freeze up the whole app. Most of the time, the fix is not rewriting your database engine—it is knowing when and where to put indexes.
01. What an index actually does
Think of a database table without indexes like a book without an index section or table of contents. To find an order with `status = 'completed'`, MySQL has to scan every single page (a full table scan). An index creates an ordered lookup structure that lets the database jump directly to matching rows.
However, adding indexes to every single column hurts write performance. Every time you insert or update a record, the database has to recalculate the index trees. Balance is key.
- Index columns frequently used in `WHERE`, `ORDER BY`, and `JOIN` conditions
- Do not index low-cardinality fields like booleans by themselves
- Foreign keys should almost always have an index
- Watch out for too many indexes on high-write tables
02. Composite indexes and column order
A common mistake is creating two separate indexes for two columns that are always queried together, like `user_id` and `created_at`. A composite index on `(user_id, created_at)` is much more efficient.
The order of columns in a composite index matters: place the equality checks first (like `user_id = 5`) and the range or sorting column last (like `created_at DESC`).
03. Using EXPLAIN before making changes
Before guessing which index to add, run `EXPLAIN` on your raw SQL query. Look at the `type` and `rows` columns. If `type` says `ALL`, MySQL is doing a full table scan. After adding your migration index, check `EXPLAIN` again to ensure the query is using the index and checking a fraction of the rows.
Understanding indexes takes the mystery out of database optimization. Spending twenty minutes analyzing slow query logs and adding clean composite indexes can save you hundreds of dollars in server costs.