Database Indexing Explained: Why Your Queries Are Slow and How to Fix Them

A query that takes 40 milliseconds on a table with 10,000 rows can take 40 seconds on the same table with 10 million rows, running the exact same SQL against the exact same schema. Nothing about the query changed. What changed is that the database is now scanning every row to find the ones that match, instead of jumping straight to them. That's the entire indexing problem in one sentence, and it's the single most common performance issue we see when we're brought in to fix a web application that "used to be fast."

Indexes don't get discussed much in day-to-day development because ORMs hide them, local development databases are small enough that missing indexes don't matter, and everything works fine until real user data shows up. Then a dashboard that loaded instantly in staging takes twelve seconds in production, and nobody on the team can say why.

What an index actually is

A database table without an index is a list. To find a row matching a condition, the database reads every row in order and checks each one — this is called a sequential scan (or table scan). It works, but it's O(n): double the rows, double the time.

An index is a separate, sorted data structure — almost always a B-tree — that maps column values to the physical location of the rows containing them. Instead of reading a million rows to find the ones where email = 'x@example.com', the database walks a tree with a handful of comparisons (typically log₂ of the row count, so a few dozen steps even at millions of rows) and jumps directly to the matching row.

The trade-off is that an index isn't free. It's an additional structure that must be updated on every INSERT, UPDATE, and DELETE that touches the indexed column, and it takes its own disk space — often 10-20% of the table size per index. This is why the fix for slow queries is never "index everything." It's "index deliberately."

Reading an execution plan instead of guessing

The only reliable way to diagnose a slow query is to ask the database what it's actually doing, using EXPLAIN (or EXPLAIN ANALYZE, which actually runs the query and reports real timings rather than estimates). This is the step most teams skip, and it's the one that turns query optimization from guesswork into a five-minute diagnosis.

The output tells you which of a few strategies the database chose:

• Seq Scan — reading the whole table. Fine for small tables or queries that genuinely need most rows; a red flag on a large table with a selective WHERE clause.
• Index Scan — using an index to find matching rows, then fetching the full row data for each match.
• Index Only Scan — the index itself contains every column the query needs, so the database never touches the table at all. This is the fastest possible outcome.
• Bitmap Heap Scan — a hybrid used when a query matches enough rows that jumping to each one individually would be wasteful, so the database builds a bitmap of matching pages first.

The line to watch in EXPLAIN ANALYZE output isn't just which scan type was chosen — it's the gap between the estimated row count and the actual row count. A large gap usually means the query planner's statistics are stale (fixed with ANALYZE on most databases), which causes it to choose the wrong strategy even when the right index exists.

The queries that actually need an index

Not every column deserves one. Indexes earn their keep on:

• Columns in WHERE clauses, especially ones used to filter down to a small fraction of the table — status = 'active', user_id = 42, created_at > '2025-01-01'.
• Foreign key columns. Most databases do not automatically index foreign keys (Postgres notably does not), so every JOIN on an unindexed foreign key is a sequential scan on one side of the join. This is the single most common missing index we find in client codebases.
• Columns in ORDER BY. An index matching the sort order lets the database read rows in order directly rather than scanning everything and sorting afterward.
• Columns in JOIN ... ON conditions that aren't already covered by a primary or foreign key index.

Indexes are usually wasted on: columns rarely used in filters, columns with very low cardinality (a boolean or a three-value status enum gains little from a B-tree, since the index still has to return a third or half of the table), and small tables where a sequential scan is already fast enough that an index changes nothing perceptible.

Composite indexes and column order

When a query filters on more than one column, a single composite index covering both, in the right order, beats two separate single-column indexes almost every time. The rule of thumb — and it trips up a lot of teams — is that column order in a composite index matters enormously. An index on (customer_id, created_at) efficiently serves WHERE customer_id = 5 AND created_at > '2025-01-01', and also serves WHERE customer_id = 5 alone. It does not efficiently serve WHERE created_at > '2025-01-01' alone, because a B-tree sorted first by customer_id gives you no shortcut for jumping to a date range across all customers.

A useful mental model: put the column used for equality checks first, then the column used for range checks or sorting. If a query does WHERE status = 'shipped' ORDER BY created_at DESC, the index (status, created_at) lets the database find the 'shipped' rows and read them already in date order — no separate sort step needed.

Covering indexes and index-only scans

A covering index includes every column a query needs — both the filter columns and the columns being selected — so the database can answer the query from the index alone, without a second trip to fetch the full row. Postgres supports this with the INCLUDE clause on an index; MySQL's InnoDB achieves it naturally for the primary key and can be extended with composite indexes.

This matters most on high-traffic read paths: a dashboard query that runs on every page load, an API endpoint hit thousands of times a minute, an autocomplete search box. Shaving the row-fetch step off a query that runs constantly compounds into a real difference in database load and page response time.

The cost side: what indexes take away

Every index added is a write-path cost. For a table with heavy insert or update traffic — an events table, an orders table, anything logging activity — five or six indexes can visibly slow down writes, because each write now has to update six B-trees instead of one. We've seen teams over-correct after a "the dashboard is slow" complaint by adding an index for every column mentioned in every report query, only to find their checkout flow got slower because the orders table now carries eight indexes on a table with constant inserts.

The fix isn't zero indexes — it's matching index count to actual read/write ratio. A table read constantly and written rarely (a product catalog, a content table) can carry more indexes cheaply. A table written constantly and read occasionally (an audit log, a raw events table) should carry the fewest indexes that make its real queries work, often just the primary key and one or two that support actual reporting.

Partial and specialized indexes

Most relational databases support partial indexes — an index that only covers rows matching a condition. If 95% of queries against an orders table only ever look at orders from the last 90 days, or only ever look at status != 'archived', a partial index on that subset is both smaller and faster than a full-table index, and it costs less to maintain on writes to rows outside that subset.

Beyond the standard B-tree, most databases offer index types suited to specific data shapes: GIN indexes in Postgres for full-text search and JSONB columns, hash indexes for pure equality lookups, BRIN indexes for very large, naturally-ordered tables like time-series data where a lightweight index on block ranges beats a full B-tree. Reaching for one of these matters once a query pattern is unusual enough that a plain B-tree isn't the right tool — full-text search against a LIKE '%term%' query, for instance, will never use a standard B-tree efficiently regardless of how it's built.

A practical process for fixing slow queries

When we take over a codebase with performance complaints, the process is the same regardless of the stack:

1. Find the slow queries first, using the database's own slow-query log or an APM tool, rather than guessing from the code. Query time in production under real data volume is the only number that matters.
2. Run EXPLAIN ANALYZE on each one and look for sequential scans on large tables, and for large gaps between estimated and actual row counts.
3. Check for missing foreign key indexes — this alone resolves a surprising share of join-heavy slowness.
4. Add composite indexes matched to the actual WHERE/ORDER BY clauses of the slow queries, not to the schema in the abstract.
5. Re-run EXPLAIN ANALYZE to confirm the plan changed, not just that the index exists — an index that isn't selective enough, or that doesn't match column order, can go entirely unused even after creation.
6. Watch write-path impact on the affected tables after deploying, particularly on tables with heavy insert or update traffic.

This is a normal part of the maintenance work on any application that's grown past its first few months of real usage — schemas designed against a nearly-empty development database rarely hold up unmodified once real data volume arrives, and that's expected, not a sign anything was built wrong the first time. It's part of the ongoing engineering work behind any application handling meaningful traffic, and it's one of the reasons we treat performance tuning as part of a maintenance relationship with clients rather than a one-time deliverable — the queries that matter shift as usage patterns and data volume shift.

Monitoring index health, not just adding indexes once

Indexing isn't a one-time task that gets marked complete after a launch. As a table's data distribution shifts — a status column that used to be mostly 'pending' becomes mostly 'completed', a region column that used to have ten values grows to fifty — a query planner's earlier choice of index can stop being the optimal one, even though nothing about the query or the index itself changed. Most relational databases expose statistics views (pg_stat_user_indexes in Postgres, similar system tables elsewhere) showing how often each index is actually used; periodically checking these surfaces two useful signals: indexes that are barely ever used, which are pure write-path cost with no offsetting benefit and are usually safe to drop, and tables with heavy sequential-scan activity despite having indexes, which usually means the existing indexes don't match the queries actually being run against that table anymore. Treating this as a recurring five-minute check during regular maintenance, rather than a one-time setup step, catches the slow drift that otherwise shows up months later as a customer complaint about a report that "used to be fast."

If a specific dashboard, report, or API endpoint in your product has gotten slower as your data has grown, that's usually a targeted, fixable problem rather than a sign of a deeper architectural issue — and it's often resolved in hours, not a rebuild.