SQL Playgroundlearn SQL safely, in your browser

Indexes: What They Speed Up and What They Cost

An index is a second, sorted copy of part of your data with pointers back to the rows. It turns a full scan into a lookup — for exactly the queries whose filter matches the index.

The library analogy is accurate

Finding every mention of a word in a library by reading every book is a table scan. A card catalogue that maps word to page is an index: you go straight to the pages. The cost is that every time a book changes, the catalogue must be updated too.

The index must match the filter

An index on (last_name) accelerates WHERE last_name = ?. It does not accelerate WHERE lower(last_name) = ?, because the index stores the original case. Wrapping a column in a function usually makes the index unusable — that single habit causes a large share of slow queries.

Column order in a composite index matters

An index on (a, b) helps WHERE a = ? and WHERE a = ? AND b = ?. It does not help WHERE b = ? alone — the same way a phone book sorted by surname then first name is useless if you only know the first name.

Writes pay for reads

Each INSERT, UPDATE and DELETE now has to maintain every index on the table. On a write-heavy table, five indexes can dominate the cost of an insert. Indexes are a trade, not a free win.

Check before you assume

In PostgreSQL and MariaDB, prefix the query with EXPLAIN. If the plan says sequential scan (or type: ALL in MariaDB) on a large table, the index is not being used — either it does not exist, does not match, or the planner judged a scan cheaper.

Try it in the playground — free and private

← All SQL guides