Database Indexes: Why Your Query Is Slow

Your query was fast on 10,000 rows. Now there are 10 million and it takes 8 seconds. Nothing changed in the code. What changed is that the database is now scanning the entire table to find the rows you want, and an index is the fix.

What an index actually is

An index is a sorted copy of one or more columns, stored separately and kept in sync with the table. Most databases use a B-tree: a balanced structure that lets the engine binary-search to a value instead of reading every row.

Without an index, a lookup is a full table scan in O of n. With a B-tree index, it drops to O of log n. On a million rows that is the difference between a million reads and roughly twenty.

When an index pays off

Reach for an index when a column shows up in:

• WHERE filters on selective values (a unique user id, an email)
• JOIN conditions linking two tables
• ORDER BY clauses, so the engine reads rows pre-sorted
• Foreign keys, which are not auto-indexed in many engines

The key word is selective. An index on a boolean column with two values barely helps, because half the table still matches.

Composite indexes and the leftmost rule

An index on (tenant_id, created_at) can serve a query filtering on tenant_id alone, or on both columns together. It cannot efficiently serve a query filtering on created_at alone, because the index is sorted by tenant_id first.

This is the leftmost-prefix rule: a composite index works left to right. Order your columns by how you actually query them, most-filtered first.

The traps that silently kill your index

Indexes are easy to disable by accident:

• Functions on the column. WHERE lower(email) = 'x' cannot use a plain index on email. You need a functional index, or store the value already-lowercased.
• Leading wildcards. LIKE '%term' cannot seek a B-tree. LIKE 'term%' can.
• Type mismatches. Comparing an indexed integer column to a string forces a cast and a scan.
• Low selectivity. The planner may correctly decide a scan is cheaper than an index on a column where most rows match.

Read the query plan, do not guess

Never optimize by intuition. Run EXPLAIN ANALYZE (Postgres) or EXPLAIN (MySQL). Look for Seq Scan or type: ALL where you expected an index seek. The plan tells you exactly what the engine decided and why.

The cost side

Indexes are not free. Every INSERT, UPDATE, and DELETE must also update every relevant index, so a write-heavy table with ten indexes pays a real penalty. They also consume disk and memory. Index what your reads need, drop what nothing uses.

The instinct to reason about access patterns and growth is the same one system design problems drill, and indexing decisions sit right at the boundary of algorithms and storage engineering. If you want a structured climb through both, the roadmaps sequence it.

Think you can spot the missing index before the planner does? Put it to the test against an AI tuned to your tier at Cruxible and find out whether you can still beat the machine.