Database indexing basics

An index is a sorted lookup structure that lets the database find rows without scanning the whole table. It is the single biggest lever for query speed, and the most commonly overlooked.

The book analogy

Finding a topic by flipping every page is a full table scan. Using the index at the back of the book to jump straight to the page is an index lookup. Same data, wildly different effort.

When an index helps

Indexes speed up:

• WHERE filters on the indexed column
• JOIN conditions
• ORDER BY and GROUP BY on indexed columns

CREATE INDEX idx_users_email ON users (email);

-- Now this is a fast lookup instead of a scan
SELECT * FROM users WHERE email = 'a@example.com';

Composite indexes and column order

A multi-column index is ordered left to right. An index on (status, created_at) helps queries that filter on status, or on status and created_at together, but not one that filters on created_at alone.

CREATE INDEX idx_orders_status_date ON orders (status, created_at);

Rule of thumb: put the most selective column, or the one you always filter on, first.

The cost

Indexes are not free:

• Every INSERT, UPDATE, and DELETE must also update the index
• Indexes take disk space
• Too many indexes slow down writes and confuse the query planner

Reading the plan

Ask the database what it is doing rather than guessing.