MySQL Indexes and Query Performance

An index is a data structure that trades storage and write cost for faster reads. The right index follows the filtering, join, and ordering pattern of a real query.

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);

The equality predicate comes first, followed by the range and ordering column. Index design depends on the full query, not on individual columns in isolation.

Read the Execution Plan

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

Look for the chosen index, estimated and actual row counts, loops, sorting, temporary tables, and expensive nested operations. Large estimate errors may indicate stale statistics or correlated data.

Covering Indexes

If the index contains every column needed by the query, MySQL may satisfy it without reading the base table. Covering indexes can be powerful for hot read paths but make indexes wider and writes more expensive.

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.