Imagine searching a book for a name when it has no index: you would flip through every page. That is exactly what MySQL does when it finds no index suited to your query, and it is called a full table scan.

What is an index?

A sorted structure the database keeps for chosen columns, so it can jump straight to the rows you need without reading the whole table.

When to add one

  • Columns in a frequently repeated WHERE clause.
  • JOIN columns, including foreign keys.
  • ORDER BY columns in large queries.

Composite indexes and column order

If your query filters by status and then sorts by date, an index on (status, created_at) fits better than two separate indexes. Column order matters: the index is used starting from its first column.

CREATE INDEX idx_orders_status_date ON orders (status, created_at);
EXPLAIN SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;

Use EXPLAIN

Put it in front of a query to see whether an index was used and how many rows will be examined. Seeing ALL as the access type means a full scan.

Do not overdo it

Every index slows inserts and updates and uses space. Add what you truly need and drop indexes that are never used.

Measure before you index: an index that serves no real query is dead weight.