On this page
What Is Database Indexing (and Why Is My Query Slow)?
A database index is a lookup structure that lets queries find rows without scanning the whole table. Learn how indexes work and why queries are slow.
Quick answer
- An index is a data structure that lets the database find rows quickly, like a book’s index instead of reading every page.
- Without an index, a query scans the entire table, which gets slower as the table grows.
- Indexes speed up reads but slow down writes and take storage, so they’re a trade-off, not a free win.
What is a database index?
A database index is an auxiliary data structure (usually a B-tree) that the database maintains alongside a table, mapping the values of one or more columns to the rows that contain them. When a query filters or sorts by an indexed column, the database walks the index to jump straight to matching rows instead of scanning every row in the table. The analogy is precise: an index is the book’s index, not the book.
Why queries are slow without one
Without an index, a query like SELECT * FROM users WHERE email = 'x' forces a full table scan: the database reads every row and checks the condition. That’s fine at a thousand rows and slow at ten million. The slowdown isn’t the query changing — it’s the table growing while the access pattern stays linear. This is the most common cause of “it was fast, now it’s not.”
The trade-off
Indexes aren’t free. Each one must be updated on every insert, update, or delete, so writes get slower and storage grows. An index on a column you never filter or sort by is pure overhead. The craft is indexing the columns your queries actually use — the ones in WHERE, JOIN, and ORDER BY — and nothing else.
Where this bites vibecoders
AI-generated schemas often define primary keys but skip secondary indexes entirely, and the generated queries then do full scans that work fine in the demo and fall over with real data. When the app “suddenly” gets slow, the first thing to check is whether the hot queries have indexes. This is a data problem, not a code problem, and it’s invisible until scale.
Where AI coding assistants get this wrong
- Never suggesting indexes on foreign keys or frequently filtered columns.
- Adding an index to every column “for safety,” slowing writes for no benefit.
- Generating queries that can’t use an index (functions around the column, leading wildcards).
- Skipping
EXPLAINanalysis and guessing at the cause of slowness.
Checklist
- Identify your hot queries (the ones run constantly or on large tables).
- Index the columns those queries filter, join, and sort by.
- Verify with
EXPLAINthat the query uses the index, not a full scan. - Avoid indexes on columns you don’t query.
- Remember the write cost: index deliberately, not exhaustively.
FAQ
What is a full table scan?
A full table scan reads every row in a table to find matches, because no index helps the query. It’s fine on small tables and a performance killer on large ones. EXPLAIN output shows “Seq Scan” (Postgres) or “ALL” (MySQL) for a full scan.
What is a primary key index?
The primary key is automatically indexed (and enforces uniqueness), so lookups by primary key are already fast. The gaps are almost always the other columns — foreign keys and frequently filtered fields — which need their own indexes.
Can too many indexes hurt?
Yes. Every index must be maintained on writes, so a write-heavy table with many indexes gets slower, and storage grows. Index what queries need, and drop indexes that aren’t used.