What Is a Database Index? The Thing That Turns a Slow Query From Seconds Into Milliseconds

A SaaS admin panel's "Orders" page is loading, and the spinner just keeps turning — six, seven seconds. The user refreshes, checks their connection, tries again. The problem is still there. A developer checks the logs and finds a single SQL query behind it: `SELECT * FROM orders WHERE customer_id = 482913 ORDER BY created_at DESC LIMIT 20`. This looks like a trivial query, but on a table with 2 million rows it takes seconds to run. One line gets added — `CREATE INDEX idx_orders_customer ON orders(customer_id, created_at);` — and the same query drops to 9 milliseconds. That moment is the most concrete answer to what a database index actually is: an index is a structure that fundamentally changes how many rows the database has to look at to answer a question. This piece walks through what an index actually does, where that speed difference comes from, when you should add one, and where it isn't free — through a real-world scenario.

##What Is a Database Index, and What Does It Actually Do?

Think of the index at the back of a book. To find every page mentioning "Kublai Khan," you don't read the book cover to cover; you flip to the index, find "K," see "Kublai Khan: pages 214, 389," and jump straight there. A database index does exactly this: it keeps the values of one or more columns pre-sorted in a structure — usually a B-tree — and maps each value to a pointer that points to where the matching row actually lives on disk. Put an index on `customer_id`, and the database engine no longer has to scan the entire table to find rows where `customer_id = 482913`; it walks down the sorted structure with something close to a binary search, follows the pointer, and lands on the right row. Without an index, the engine has exactly one option: read every row in order and check whether it matches. That's called a full table scan, and it slows down linearly as the table grows.

##Where Does That Speed Difference Actually Come From?

The cost of a full table scan grows proportionally with the number of rows: unnoticeable at 10,000 rows, measured in seconds at 2 million, and potentially minutes at 50 million. An indexed lookup, by contrast, grows with the height of the B-tree — which barely increases even as the row count grows exponentially. In practice, on a 2-million-row table, an unindexed query has to inspect every row, while an indexed query does a few dozen comparisons and lands directly on the right one. Indexes also make sorting nearly free: because the index already stores values in order, an `ORDER BY created_at DESC` doesn't require a separate sort step — the data is already arranged that way. The same logic applies to `JOIN`s: matching rows between two tables is instant with the right index in place, and requires a full scan on both sides without one.

##A Real Scenario: A 2-Million-Row Orders Table

Let's make this concrete: a three-year-old e-commerce company has an "orders" table with 2.1 million rows. The customer support team opens a "Customer History" tab in the admin panel to look up a customer's recent orders. Behind the scenes, that page runs a query filtering by `customer_id` and sorting by `created_at`. As the company grows, the page gets slower — first 1-2 seconds, then 4-5, until support starts complaining that "the panel is freezing." A developer runs `EXPLAIN ANALYZE` on the query and sees the plan says "Seq Scan on orders": the database is scanning all 2.1 million rows every single time, with actual execution time ranging 900ms-2.5s depending on current load.

The fix is a composite index on `(customer_id, created_at)`: `CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);`. The column order here is deliberate — narrow down by `customer_id` first, then read rows that are already stored in `created_at` order for that customer. After the index is built, the same `EXPLAIN ANALYZE` shows "Index Scan using idx_orders_customer_created," and actual execution time drops to 4-12 milliseconds. For the support team, the only thing that changes is that the page now loads instantly; behind the scenes, what changed is that the database engine walks straight to the right branch instead of scanning 2.1 million rows. This is a real, common scenario where one small SQL statement eliminates an operational complaint entirely.

>Which Columns Make Good Index Candidates?

• ▸Columns frequently used in WHERE clauses (e.g. customer_id, status, email)
• ▸Columns frequently used in ORDER BY or GROUP BY (e.g. created_at)
• ▸Foreign key columns used to match rows in JOINs
• ▸High-cardinality columns — ones with many distinct values (email is a good candidate; a two-value column like is_active is a poor one)
• ▸Columns that are queried often but updated rarely — where indexes pay off the most

The same logic shows up somewhere far more common: user login. If a `users` table has no index on `email`, every login attempt makes the database scan all user rows looking for a match — at 50,000 users that delay might go unnoticed, but at 5 million users the login page visibly slows down. Putting a unique index on `email` both speeds up that lookup instantly and enforces at the database level that the same email can't be registered twice — an index sometimes buys you not just speed, but a data-integrity guarantee too.

##An Index Isn't Free: What to Watch Out For

Indexes speed up reads but raise the cost of writes. On every `INSERT`, `UPDATE`, or `DELETE`, the database engine has to update not just the table but every index on that table too. A table with 8-10 indexes means a single row insert touches 8-10 separate structures — which slows down writes and increases disk usage (an index can take up as much space as the table itself). "Just index every column, it can't hurt" is the wrong instinct: on a write-heavy, rarely-queried table, unnecessary indexes are a net performance loss.

There are three practical traps. First, column order matters in composite indexes: an index on `(customer_id, created_at)` barely helps a query like `WHERE created_at > X`, because the index is sorted by `customer_id` first — you'd need the reverse order. Second, a leading-wildcard search like `LIKE '%keyword%'` can't use a standard B-tree index at all, because the index is a sorted structure and a "contains anywhere" pattern can't be searched that way — this needs full-text search or a trigram index instead. Third, applying a function to a column in a query (`WHERE LOWER(email) = 'x'`) can skip a plain index entirely and fall back to a full scan — the fix is building an expression index on that exact expression. None of these are exotic; each shows up constantly in real projects and can be diagnosed in a few minutes with `EXPLAIN ANALYZE`.

The most practical way to catch these three traps regularly is a monthly review: pull `EXPLAIN ANALYZE` output for your 10-15 slowest production queries, and check the database's own index usage statistics (`pg_stat_user_indexes` in PostgreSQL, `sys.schema_unused_indexes` in MySQL) — dropping an unused index often delivers a bigger performance win than adding a new one.

##Frequently Asked Questions

>Should I add an index to every column?

No. Only index columns that are frequently used in `WHERE`, `JOIN`, or `ORDER BY`. Indexing rarely-queried columns, or columns with very few distinct values (like a `boolean` column), lowers write performance and increases disk usage without meaningfully speeding up reads. The rule of thumb: look at actual query logs first, find your slowest and most frequent queries, then index the columns those specific queries filter or sort by — decide by measuring, not guessing.

>Does adding an index take a live application down?

Usually not, but large tables need care. In PostgreSQL, `CREATE INDEX CONCURRENTLY` builds the index without locking the table (it takes somewhat longer, but the app keeps running); in MySQL, most InnoDB index builds are online as well. Still, on very large tables (tens of millions of rows), the build can take minutes and add load to the server — scheduling it for a low-traffic window is the safer move.

>I added an index and the query is still slow — why?

The three most common reasons: a composite index with the wrong column order, a query applying a function or type conversion that causes it to skip the index, or an ORM generating different SQL than you expect. Instead of guessing, run `EXPLAIN ANALYZE` (PostgreSQL/MySQL) and look at the actual query plan — if it says "Seq Scan" instead of "Index Scan," the index isn't being used, and the plan output usually tells you exactly why.

>How many indexes on one table is too many?

There's no fixed number — it depends on your read/write ratio. On a reporting table that's read constantly and written rarely, 10+ indexes can be reasonable; on a heavily-written transactional table (like order line items), 3-5 starts pushing the limit, and every additional index should earn its write cost. Periodically cleaning up unused indexes (most database engines track index usage statistics) is an equally important piece of maintenance.

>Do indexes also speed up COUNT(*) or search features?

Partially. A `COUNT(*)` filtered by a `WHERE` condition does speed up genuinely if that column is indexed — the engine can count matching rows straight from the index. But free-text search like "product name contains X" doesn't work well with a standard index; for that, a `tsvector`-based full-text search index in PostgreSQL, or a dedicated search engine like Elasticsearch, is the right tool. An index isn't the universal fix for every speed problem — knowing which structure fits which query type matters just as much as adding the right index in the first place.

At its core, what an index does is simple: it tells the database "here's how to answer this question doing less work every time." When a query slows down, this is almost always the first place to look — without changing your architecture or upgrading your server, adding the right structure can turn a multi-second delay into milliseconds. If you want a second pair of eyes on a similar performance issue in your own project, reach out through /contact; if you're curious how I typically approach SaaS and automation projects, /services and /process-pricing have more detail.