SQL Indexes Explained: When They Help and When They Hurt

Quick Answer

An index is a sorted structure, usually a B-tree, that lets the database jump straight to matching rows instead of scanning the whole table. It turns a full scan into roughly logarithmic lookups, which is why a query can go from seconds to milliseconds. The cost is that every insert, update and delete must also maintain the index, and each one takes disk space. Index the columns you filter, join and sort on — not every column, and not blindly.

What an Index Actually Does

Without an index, finding rows means reading every row — a full table scan. On a million-row table that is a million reads to find one record.

An index is a separate sorted structure holding the indexed column's values along with pointers to the rows. Because it is sorted, the database can search it the way you would search a dictionary: jump to roughly the right place, narrow down, arrive.

-- Before: scans every row
SELECT * FROM users WHERE email = 'riya@example.com';

CREATE INDEX idx_users_email ON users(email);

-- After: descends a B-tree, a handful of reads

The usual structure is a B-tree, which stays balanced as data changes so lookups remain predictable, and keeps values in order — which is what lets an index serve range queries (BETWEEN, >, <) and ORDER BY, not just exact matches.

The book analogy holds well: the index at the back of a textbook is a sorted list of terms with page numbers. It is extra pages, it must be updated if the book is revised, and it saves you reading the whole thing to find one topic.

The Cost Nobody Mentions Until Production

Indexes are not free, and the price is paid on writes.

Every INSERT must add an entry to every index on that table. Every DELETE must remove them. Every UPDATE to an indexed column must move the entry to its new sorted position. A table with eight indexes does roughly eight extra pieces of work on every single write.

They also consume disk and memory. An index on a large text column can approach the size of the data itself, and indexes compete for the same cache your table data wants.

This produces a real failure mode: someone profiles slow reads, adds indexes to a dozen columns, reads improve, and then inserts slow to a crawl. On a write-heavy table — a logging or events table — over-indexing can be worse than the original problem.

The rule that follows is simple. Index the columns you actually filter, join or sort on. Not every column. Not "just in case". If a query does not exist, the index for it should not either.

Composite Indexes and Why Column Order Matters

An index can cover several columns, and the order you list them in is not cosmetic.

CREATE INDEX idx_city_age ON users(city, age);

This index sorts by city first, then by age within each city. Think of a phone book ordered by surname then first name.

That structure serves these queries:

• WHERE city = 'Pune' — uses the index.
• WHERE city = 'Pune' AND age > 25 — uses the index fully.

But not this one:

• WHERE age > 25 — cannot use it.

Searching a phone book for everyone named "Riya" regardless of surname means reading all of it — the ordering does not help you. This is the leftmost prefix rule: an index on (a, b, c) supports queries on a, on a+b, and on a+b+c, but not on b alone or c alone.

So put the column you always filter on first. A well-designed composite index can replace several single-column ones, which reduces write cost as well.

A covering index is the bonus case: if the index contains every column a query needs, the database answers entirely from the index without touching the table at all.

Why the Database Ignores Your Index

You add an index, the query is still slow, and the plan shows a full scan. Usually it is one of these.

A function on the indexed column. The index stores the raw values, not the transformed ones.

-- Index cannot be used: the values indexed are not the lowercased ones
WHERE LOWER(email) = 'riya@example.com'

-- Either store a normalised column, or create a functional index
CREATE INDEX idx_email_lower ON users(LOWER(email));   -- PostgreSQL

A leading wildcard. LIKE 'riya%' can use an index, because it fixes the start. LIKE '%riya' cannot, because the sorted order gives no way to narrow down — that needs a full-text index instead.

Low selectivity. If a column has two values and each covers half the table, the index is useless — jumping between the index and the table for half the rows is slower than just reading the table. Optimisers know this and will correctly ignore your index.

Type mismatches. Comparing a varchar column to a number can force an implicit conversion and disable the index.

The way to find out is to ask the database rather than guess. EXPLAIN — or EXPLAIN ANALYZE in PostgreSQL — shows the chosen plan and whether it is a scan or an index lookup. Reading a query plan is one of the highest-value skills in backend work.

Practical Rules Worth Following

• Primary keys are indexed automatically. Do not add another one.
• Foreign keys are not always. MySQL's InnoDB creates one; PostgreSQL does not. An unindexed foreign key makes joins and cascading deletes slow, and this is one of the most common missed indexes in real systems.
• Index what appears in WHERE, JOIN and ORDER BY. A sort on an indexed column can skip sorting entirely.
• Measure before and after with EXPLAIN. Adding indexes hopefully is how tables end up with a dozen unused ones.
• Watch write-heavy tables. On an events or logs table, keep indexes to the minimum that queries genuinely need.
• Drop indexes nothing uses. Both PostgreSQL and MySQL can report index usage statistics; unused indexes are pure cost.

The mental model to keep: an index trades write speed and disk space for read speed. That trade is overwhelmingly worth it on the columns you query, and a pure loss on the ones you do not.

Frequently Asked Questions