What Is an Index?
Why an index makes a query faster, in plain terms — now that you've written enough queries to feel one run slowly.
Overview
An index is a separate, ordered data structure that lets the database jump directly to the rows matching a query instead of reading every row in the table to check. It's the single most direct payoff of everything covered so far: it exists specifically to avoid paying the full disk-latency cost, row by row, that the NVMe SSDA solid-state drive using the Non-Volatile Memory Express protocol for high-speed flash storage access, faster than older SATA SSDs.Learn more/HDDHard Disk Drive — magnetic spinning-platter storage. A physical read/write arm has to move for every seek, which is why HDD latency (~5–10 ms) is orders of magnitude slower than SSD or RAM.Learn more numbers earlier in this journey measured — and it's the reason a well-designed query on a huge table can still come back in milliseconds.
Why It Exists
Without an index, a `WHERE`, `JOIN`, or `ORDER BY` on a large table forces a full table scan — the engine reads every single row to check whether it matches, paying the storage-latency cost of every row along the way. On a million-row table, that's the difference between a query that returns instantly and one that visibly hangs. An index exists to turn that linear, read-everything search into something closer to a direct lookup.
Real World Example
A `users` table with a million rows and no index on `email`: running `WHERE email = 'someone@example.com'` forces the engine to check every single row, one at a time, until it either finds a match or exhausts the table — noticeably slow, and it gets worse as the table grows. With an index on `email`, the same query is close to instant, because the engine can jump almost directly to the matching row instead of scanning past the other 999,999 — the same difference as looking up a word in a dictionary's index versus reading the entire book from the first page.
Example Data
users (imagine 1,000,000 rows) — WHERE email = 'michael.carter@example.com'
| id | name | |
|---|---|---|
| 1 | sarah.johnson@example.com | Sarah Johnson |
| 2 | emma.davis@example.com | Emma Davis |
| 3 | michael.carter@example.com | Michael Carter |
| 4 | james.wilson@example.com | James Wilson |
How It Works
Most relational engines implement indexes as a B+ TreeA balanced, disk-page-based tree structure used by relational engines like Postgres and InnoDB for in-place updates and fast range scans.Learn more — a balanced, sorted tree structure that maps a column's values to the physical location of the rows that hold them, letting the engine binary-search down to a match in a handful of steps instead of checking every row one by one. That speedup isn't free: every `INSERT`, `UPDATE`, or `DELETE` on the table also has to update every index built on it, so each additional index is a small, permanent tax on every future write, in exchange for faster reads on that specific column. Deciding which columns to index is genuinely a trade-off, not a default 'more is better' decision.
Diagram
The same query, with and without an index on the filtered column
Query: WHERE email = 'x'
No Index
full table scan — checks every row, O(n)
Index on email
B+ Tree lookup — jumps to the match, O(log n)
Matching row returned
Common Mistakes
Indexing every column 'just in case' it's queried later
Why: Every index slows down every write to that table and consumes disk space permanently, whether or not the index actually gets used by a real query.
Fix: Index columns that are actually filtered, joined, or sorted on in real, frequent queries — verify with EXPLAIN, don't guess ahead of time.
Adding an index and assuming the query now uses it
Why: A query can fail to use an otherwise-correct index for several reasons — wrapping the indexed column in a function, using the wrong leading column in a composite index, or a type mismatch — and the engine silently falls back to a full scan.
Fix: Check the actual query plan with EXPLAIN or EXPLAIN ANALYZE — never assume an index is being used just because it exists.
Indexing a low-cardinality column, like a boolean is_active flag, expecting a big speedup
Why: With only two or three distinct values spread across millions of rows, an index barely narrows anything down — the engine may reasonably choose to scan the table anyway instead of using it.
Fix: Indexes pay off most on high-cardinality columns used in selective filters — narrow the field down to a small fraction of the table, not a large one.
Interview Questions
In plain terms, what does a database index do?
It's a separate, sorted structure that lets the database jump directly to matching rows instead of checking every row in the table one by one — similar to using a book's index instead of reading every page.
Why isn't it a good idea to index every column in a table?
Every index has to be updated on every insert, update, and delete that touches that table, so each additional index adds a permanent cost to every write. Indexes should be added deliberately, for columns that are actually filtered, joined, or sorted on in real queries — not by default.
A query has an index on the exact column it filters on, but EXPLAIN shows a full table scan anyway. What would you check?
Whether the column is wrapped in a function or type-cast in the query (which usually prevents standard index use), whether it's a composite index and the query isn't filtering on its leading column, whether the table is small enough that the engine's cost estimator reasonably prefers a scan, and whether the column's statistics are stale enough that the planner is misjudging its selectivity.
Production Best Practices
Do
✓Index columns that are actually filtered, joined, or sorted on in real, frequent queries.
✓Verify index usage with EXPLAIN instead of assuming it.
✓Prioritize indexing high-cardinality columns used in selective filters.
Don't
✗Don't index every column 'just in case' — each one taxes every future write.
✗Don't assume an index is being used without checking the query plan.
✗Don't expect a meaningful speedup from indexing a low-cardinality column like a boolean flag.
Comparison
| Read Speed on Filtered Column | Write Cost | Best For | |
|---|---|---|---|
| No Index | Slow — full scan | None | Small tables, rarely-queried columns |
| Single-Column Index | Fast | One index to update per write | Columns filtered/joined/sorted on often |
| Composite Index | Fast, if the leading column matches | One index to update per write | Queries that consistently filter on the same multiple columns together |