MySQL Indexes and Query Performance

An index trades some disk space and slower writes for much faster reads. The trick is building the index for the queries you actually run, matching how they filter, join, and sort. A random index on every column mostly just slows down your inserts.

Composite index order

SELECT id, name, created_at
FROM customers
WHERE status = 'active'
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 50;

CREATE INDEX idx_customers_status_created
    ON customers (status, created_at DESC);

Equality columns go first, then the range or sort column. You have to design the index around the whole query, not one column at a time.

Read the execution plan

EXPLAIN ANALYZE
SELECT id, name, created_at
FROM customers
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 50;

What I look for: which index it picked, estimated vs. actual rows, loops, sorting, temporary tables, and any nested operation doing way more work than it should. If the estimates are way off from reality, the statistics might be stale (ANALYZE TABLE) or the data is correlated in a way the optimizer doesn't expect.

Covering indexes

If the index has every column the query needs, MySQL can answer straight from the index without touching the table at all. That's great for queries that run constantly, but it makes the index bigger and every write a bit more expensive. Use it where it counts.

Common index problems

• Creating one index for every column without considering query combinations.
• Using a function on the indexed column in the predicate.
• Leading wildcard searches such as LIKE '%term'.
• Sorting large unbounded result sets.
• Fetching every column with SELECT *.
• Keeping redundant prefix indexes after adding a composite index.
• Adding indexes without measuring write and storage impact.

Avoid functions on indexed columns

-- Harder to use a normal index efficiently
WHERE DATE(created_at) = '2026-07-16'

-- Range form
WHERE created_at >= '2026-07-16 00:00:00'
  AND created_at <  '2026-07-17 00:00:00'

Slow query investigation

1. Capture the exact SQL shape and execution time.
2. Check frequency; a 50 ms query executed 10,000 times may matter more than one 2-second report.
3. Run EXPLAIN ANALYZE with representative data.
4. Review indexes, row estimates, sorting, and temporary work.
5. Check application behavior for N+1 queries and repeated calls.
6. Apply one change and measure again.