SQL Indexes Explained: How They Speed Up Queries and When They Do Not

A query that runs instantly on a hundred rows can crawl on a million. Indexes are the most common way to fix that, and a favourite interview topic. Here is how they work and how to use them sensibly.

What an index is

An index is a separate data structure that the database maintains alongside a table so it can find rows without reading every one. The book analogy fits well: instead of reading every page to find a word, you use the index at the back to jump to the right pages. Most relational databases use a B-tree structure by default, which keeps values sorted and makes lookups, range searches and sorting efficient.

Without and with an index

Suppose you often run:

SELECT * FROM users WHERE email = 'asha@example.com';

Without an index on email, the database checks every row, which is called a full table scan. With an index, it can go almost straight to the matching row.

CREATE INDEX idx_users_email ON users (email);

Primary keys are indexed automatically, and unique constraints are normally backed by an index too. Foreign key columns are handled differently: MySQL's InnoDB creates an index for them, while PostgreSQL does not, so check your database when joins on a foreign key are slow.

What indexes cost

Indexes are not free.

• Writes get slower. Every insert, update and delete must also update the indexes.
• They use storage. Each index is another structure on disk and in memory.

So do not index every column. Add indexes for the queries that matter.

Which columns to index

Look at columns used in:

• WHERE filters that are selective, meaning they match a small part of the table.
• JOIN conditions, so lookups on the joined table are fast.
• ORDER BY and GROUP BY, where a suitable index can avoid a separate sort.

A column with very few distinct values, such as a yes/no flag, is rarely a good index on its own because it does not narrow the search much.

Composite indexes and column order

An index can cover several columns.

CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

The order matters. This index helps queries that filter by customer_id, or by customer_id and created_at, because it is sorted by customer first. It generally does not help a query that filters only by created_at. Put the column you filter by equality first, then range or sort columns.

When an index is not used

Even with an index, some patterns prevent its use:

• Wrapping the column in a function, such as WHERE LOWER(email) = 'x', unless you create an index on that expression.
• A leading wildcard, such as LIKE '%example.com'.
• Type mismatches that force conversions.
• Filters that match most of the table, where scanning is cheaper.

Check with EXPLAIN

Do not guess. Prefix a query with EXPLAIN to see the plan, and look for whether the database chose an index scan or a full table scan. The output format differs between MySQL and PostgreSQL, but the idea is the same. Test on realistic amounts of data, because a plan on ten rows tells you little.

Practise

The SQL Joins & Aggregation quiz includes an index-friendly-queries question, and MongoDB uses the same idea, as shown in the MongoDB modelling and indexes quiz. For the join side of performance, read SQL joins explained.

Key takeaways

• An index lets the database find rows without scanning the table.
• Indexes speed up reads and slow down writes, so add them deliberately.
• Column order in composite indexes matters.
• Functions on columns and leading wildcards can prevent index use.
• Always confirm with EXPLAIN.