MySQL Indexing: A Practical Guide for Developers

Most slow applications we are asked to look at are slow because of a handful of missing indexes — not because the database is "too small". Indexing is a design decision, and it pays to understand it properly.

What an index actually is

An index is a sorted copy of selected columns that lets MySQL find rows without reading the whole table. Think of the index at the back of a book: a little extra storage, a massive reduction in pages read.

Index the columns you filter and join on

-- Slow: full table scan
SELECT * FROM invoices WHERE status = 'unpaid' ORDER BY created_at DESC;

-- Fixed
CREATE INDEX idx_invoices_status_created ON invoices (status, created_at);

The column order matters. Put equality conditions first, then the column you sort or range on.

Composite indexes work left to right

An index on (status, created_at) serves queries filtering on status, and queries filtering on status and sorting by created_at. It does not serve a query that filters only on created_at. Check your real queries with EXPLAIN before adding indexes.

EXPLAIN SELECT * FROM invoices WHERE status = 'unpaid';

Look for type: ref or range and a sensible key. type: ALL means a full scan.

Do not index everything

Every index slows down writes and consumes storage. On a table with heavy inserts — stream events, audit logs, sensor data — each extra index is a real cost. Index for your queries, not for imagination.

Watch out for these patterns

Functions on indexed columns defeat the index:

-- Cannot use an index on created_at
WHERE YEAR(created_at) = 2026

-- Index-friendly alternative
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

Leading wildcards break LIKE indexes:

WHERE name LIKE '%clinic%'   -- full scan
WHERE name LIKE 'clinic%'    -- can use an index

Implicit type conversion hides indexes: storing a string in an integer column, or comparing a VARCHAR to a number, forces MySQL to convert every row.

Use covering indexes for hot queries

If a query only needs indexed columns, MySQL answers it from the index alone without touching the table:

CREATE INDEX idx_cover ON invoices (status, created_at, total);
SELECT status, created_at, total FROM invoices WHERE status = 'unpaid';

Measure, do not guess

Enable the slow query log, review it weekly in production, and fix the queries that actually hurt. Indexes added by speculation tend to be wrong indexes.

The goal is not a fast database. The goal is a database whose query plan you understand.