Use indexes deliberately

An index gives the database another structure for finding rows without scanning the entire table.

Suppose the application repeatedly loads one customer’s recent orders:

SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

A composite index can support both the filter and the requested order:

CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC);

Column order matters. This index starts with customer_id, so it is a natural fit for queries that first narrow rows by customer. It may be less useful for a query that filters only by created_at.

An index is not a command that forces the database to use it. The query planner estimates whether an index lookup is cheaper than scanning the table. A scan can be faster for a small table or a condition that matches most rows.

Inspect the plan instead of guessing:

EXPLAIN
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

The exact EXPLAIN output differs between databases. Test with representative data: an empty development table tells you little about production behavior.

Every index consumes disk and cache space. The database must also maintain it during INSERT, UPDATE, and DELETE, so indexing every column can make writes slower without improving important queries.

Your action: run EXPLAIN for one frequent query before and after adding a matching index. Record whether the plan changes, then explain why that read benefit is worth the extra write cost.