A database index is a separate structure the database maintains alongside a table so it can jump straight to matching rows instead of reading the whole table to find them. The classic comparison is the index at the back of a book, and it holds up: instead of flipping through every page for a topic, you check the index and go straight to page 214.
The problem it solves
By default, a query like this makes the database do it the hard way:
SELECT * FROM posts WHERE slug = 'what-is-a-database-index';
With no index on slug, the database performs a sequential scan. It reads every row in the table, checks the slug on each, and keeps whatever matches. On a small table that's fine, honestly. On a table with millions of rows it means reading millions of rows to return one, and it gets a little slower with every insert, forever. The nasty part is that nothing looks wrong at first. The query is instant with a hundred rows, sluggish at a hundred thousand, and a problem at ten million, with no code change anywhere in between.
How it works
Most indexes are B-trees, a tree structure that keeps values in sorted order and stays shallow no matter how big the table gets. Sorted order is the superpower. To find one slug among millions of rows, the database walks down from the top of the tree, and each step rules out huge chunks of the data, the same way guessing "higher or lower" cuts a number range in half each time. A handful of steps replaces millions of row reads. At the bottom of the tree sits a pointer to the actual row.
Because the tree is sorted, it's good for more than exact matches. Range queries like published_at > '2026-01-01', sorting with ORDER BY, prefix matches on text: the index answers these by walking to the right spot and reading in order, which is exactly what sorted data is good at.
Once the index exists, the query planner decides on its own whether to use it, query by query. You don't rewrite anything. That's part of why adding one feels like magic: same query, same code, a thousand times faster.
The catch
Indexes aren't free, and the cost lands on writes. Every INSERT, UPDATE, or DELETE now has to update the table and every index on it, and each index takes disk space of its own. One or two indexes on a busy table is normal. A dozen "just in case" indexes means every write does thirteen jobs, and some of those indexes may never serve a single query.
So the practical rule is: index the columns your queries actually filter, join, and sort on, and nothing else. Read-heavy tables (like the posts you're reading right now) can afford indexes generously, since writes are rare. Write-heavy tables deserve more restraint.
A couple of details that pay off early:
- Primary keys are already indexed. The database creates that index for you, which is why lookups by id are always fast. Same for columns marked UNIQUE, because the index is how uniqueness gets enforced.
- Find slow queries before guessing. Postgres and MySQL both have
EXPLAIN, which shows how a query will run. If you see a sequential scan on a big table in the plan, you've found your candidate.
CREATE INDEX idx_posts_slug ON posts (slug);
One line, and the lookup from the top of this post stops scanning the table.
The takeaway
An index trades a little write work and disk space for a huge win on reads, and most applications read far more than they write, so the trade is usually excellent. The skill isn't in creating them, it's in noticing where they belong: look at the WHERE clauses your app runs most, check the plan with EXPLAIN, and index what the queries actually use. Slow queries are one of those problems that grows quietly and then all at once, and half the time the fix really is one line.