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 |