intermediatedbms

Indexes

The thing that makes queries fast without changing any data.

Key Points

A full table scan reads every row. An index scan jumps to the relevant rows. The difference is enormous on large tables.
B-tree indexes are the default in most databases. They support equality and range queries efficiently.
Hash indexes only support equality lookups. They're faster for exact matches but can't do WHERE age > 30.
Composite indexes cover multiple columns. Column order inside the index matters — the index on (city, age) doesn't help a query filtering only on age.
Every write (INSERT, UPDATE, DELETE) has to update all indexes on that table. Too many indexes slow down writes.

What it is

An index is a separate data structure the database maintains alongside a table that makes lookups on specific columns much faster. Instead of scanning every row, the database uses the index to jump directly to the relevant rows. The tradeoff is that indexes slow down writes and take up storage.

Why it exists

A table with 100 million rows and no index means every query reads all 100 million rows to find the ones you want. That's fine for small tables. For large ones it takes minutes. An index turns that into milliseconds.

Intuition

Think of a book's index at the back. If you want to find every page that mentions 'normalization', you don't read every page — you go to the index, find 'normalization', and it tells you exactly which pages to go to. A database index works the same way. The database builds and maintains this lookup structure so it doesn't have to read the whole table every time you query.

When to use

On columns that appear frequently in WHERE clauses.
On foreign key columns — most databases don't index these automatically but joins on them are very common.
On columns used in ORDER BY when you need sorted results on large tables.

When not to use

On columns with very low cardinality (like a boolean or a status column with 3 values) — the index doesn't help much when almost every value is the same.
On tables that are written to very frequently and rarely read — the write overhead isn't worth it.
On small tables where a full scan is already fast.

Common mistakes

Adding an index on every column by default. More indexes means slower writes — you're paying a cost on every INSERT and UPDATE.
Creating a composite index but putting the columns in the wrong order. An index on (last_name, first_name) helps queries filtering on last_name, but not queries filtering only on first_name.
Assuming indexes are being used. Use EXPLAIN (or EXPLAIN ANALYZE) to verify the query planner is actually using your index.

Notes

Primary key columns are automatically indexed in every SQL database.
Indexes can go stale in terms of selectivity if data distribution changes. Running ANALYZE (or equivalent) updates the statistics the query planner uses to decide whether to use an index.
Partial indexes index only a subset of rows — useful when most queries filter on a specific condition, like WHERE status = 'active'.

In practice

When pulling large datasets from a production database, slow queries are almost always a missing or misused index. Understanding indexes helps you write queries that the database can execute efficiently and helps you have informed conversations with database admins about performance.

Was this concept helpful?

Spot an error or gap? Suggest an edit →

Related concepts

sql-basicstransactions-and-acidrelational-model