If you have ever watched a query crawl for thirty seconds and then return a single row, you already understand the problem an index solves. To really grasp how database indexes work, it helps to start with what the database is forced to do without one, and then see how a single well-placed structure turns that slow crawl into a lookup that finishes before you lift your finger off the enter key. Indexes are the main reason a query over ten million rows can come back in a few milliseconds instead of grinding through the whole table.
This is a practical tour: why unindexed queries get slow, what an index actually is on disk, how the B-tree behind most indexes keeps lookups fast, and where indexes cost you. No jargon for its own sake, just the mental model you need to make good calls.
The problem: a full table scan
Imagine a users table with five million rows and a query like SELECT * FROM users WHERE email = '[email protected]'. Without an index, the database has no idea where that row lives. Its only option is a full table scan: read every row, compare the email, and keep going until it either finds a match or runs out of table.
The cost of that scan grows in a straight line with your data. Ten times more rows means roughly ten times more work. It might feel instant on a laptop with a thousand test rows, then fall over in production at five million. Nothing changed in your code. The table just got big enough for the missing index to matter.
What an index actually is
The word "index" is borrowed from books, and the metaphor is almost exact. The index at the back of a textbook is a sorted list of terms, each pointing to the pages where that term appears. You do not read the whole book to find one topic; you jump to the index, find the term in seconds because it is alphabetized, and go straight to the page.
A database index is the same idea. It is a separate, sorted data structure that stores the values of one or more columns along with a pointer to the full row on disk. Because the values are kept in order, the database can search them the way you search a sorted list, instead of reading them one by one. The trade is that the index is a second copy of that column's data, kept in sync automatically every time the table changes.
How database indexes work inside a B-tree
Most indexes you will ever create are B-tree indexes (technically a B+ tree in most engines). A B-tree is a balanced tree built to minimize disk reads, which is the expensive part of any database operation. It has three kinds of nodes: a single root at the top, internal nodes in the middle that route the search, and leaf nodes at the bottom that hold the indexed values and their row pointers.
The magic is in the fan-out. Unlike a binary tree, where each node has at most two children, a B-tree node can hold hundreds of keys and point to hundreds of children. That width keeps the tree extremely shallow. A B-tree indexing a hundred million rows is often only three or four levels deep. A lookup starts at the root, compares your value to the keys there to pick the right child, drops one level, compares again, and repeats until it reaches a leaf. Three or four comparisons, three or four disk reads, and you have the exact location of your row.
Why shallow means fast
This is the difference between linear and logarithmic cost. A full scan of a hundred million rows touches a hundred million rows. A B-tree lookup touches the height of the tree, which barely grows as the table gets bigger. Double the data and a scan doubles its work, while the tree might add a single level, or none at all. That is why an indexed lookup feels constant even as your table grows into the tens of millions.
There is a bonus baked into the structure. In a B+ tree the leaf nodes are linked together in sorted order, so once you find a starting point, you can walk sideways through the leaves. That makes range queries such as WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31' and ORDER BY clauses cheap, because the data is already sorted the way you asked for it.
The same query, with and without the index
The clearest way to feel the difference is to look at what the database planner decides to do. Every SQL engine has an EXPLAIN command that shows the plan for a query before it runs. Without an index you will see a sequential scan or full table scan over every row. Add the right index and the same query switches to an index seek that touches a tiny fraction of the table.
Getting comfortable with EXPLAIN is the single highest-leverage habit for anyone writing SQL. It tells you the truth: whether your index is actually being used, whether the engine fell back to a scan, and whether it had to do extra sorting work. When a query is slow, do not guess. Run EXPLAIN, read the plan, and let it point you at the missing index.
Composite indexes and the leftmost-prefix rule
You can index more than one column at once. A composite index on (last_name, first_name) sorts entries by last name first, then by first name within each last name, exactly like a phone book. This is powerful, but it comes with a rule that trips up a lot of engineers: the leftmost-prefix rule.
An index on (a, b, c) can serve a query that filters on a, on a and b, or on all three. It cannot help a query that filters on b alone or on c alone, because the entries are only sorted by those columns after the earlier ones are fixed. It is like trying to find everyone named "Sam" in a phone book that is sorted by last name. First names are scattered everywhere, so the sort order gives you nothing.
That makes column order a real design decision, not a stylistic one. A few reliable guidelines:
- Put columns used for equality filters (
=) before columns used for ranges (<,>,BETWEEN). - Lead with the column that appears in the most queries, since a composite index can serve any leftmost prefix of itself.
- Higher-cardinality columns, the ones with many distinct values, usually belong earlier because they narrow the search fastest.
Covering indexes: skipping the table entirely
Normally an index gets you to a row pointer, and the database then reads the full row from the table to fetch the other columns you asked for. A covering index skips that second step. If an index already contains every column a query needs, the engine can answer straight from the index and never touch the table. This is called an index-only scan, and it is one of the biggest quiet wins in query tuning.
For example, if you frequently run SELECT status FROM orders WHERE customer_id = ?, an index on (customer_id, status) covers it completely. The database finds the customer_id in the index and reads the status right there, with no trip back to the table. Fewer disk reads, faster query, same result.
The cost side: why you do not index everything
If indexes are this good, why not put one on every column? Because they are not free, and the price is paid on writes. Every index is a second copy of its data that has to stay correct, so every INSERT, UPDATE, and DELETE has to update the table and every affected index. A table with five indexes turns one write into six. On a write-heavy or logging table, that overhead adds up quickly and can become the bottleneck.
Indexes also take real disk space. On a large table, the total size of its indexes can rival or exceed the size of the table itself, which inflates storage, backups, and memory pressure. A few habits keep this in check:
- Index the columns that actually show up in
WHEREclauses,JOINconditions, andORDER BY, not every column just in case. - Be cautious with low-cardinality columns like a boolean
is_activeflag. An index that only splits the table in half rarely beats a scan. - Watch for redundant indexes. If you have a composite index on
(a, b), a separate index onaalone is usually wasted space. - Periodically look for unused indexes. An index nobody queries is pure write and storage overhead with no upside.
A short checklist for indexing well
Boiled down, good indexing comes from a handful of questions. Which queries are actually slow, according to real measurement rather than a hunch? What do those queries filter, join, and sort on? Does a composite index in the right column order cover the important ones? And is each index earning its keep against the write cost it adds? Answer those honestly and you avoid both extremes: the unindexed table that scans everything, and the over-indexed table that spends all its time maintaining indexes nobody uses.
When you are documenting a schema or sketching a system for your team, a clean visual makes these ideas land faster than a wall of DDL. If you build architecture diagrams or an internal dashboard, Thridy's soft-3D server icon and the wider 3D icon gallery are a tidy way to label the moving parts, and the category index makes it easy to find the right glyph for databases, storage, and networking.
Indexes are not magic. They are a sorted copy of your data, shaped into a shallow tree so the database can jump instead of scan. Understand that one idea and most of the day-to-day decisions, which column, what order, when to add one and when to leave it out, stop being guesswork and start being obvious.