Database Indexing Basics

A query that took 4 milliseconds a year ago now burns 800. The table has grown from 40,000 rows to 40 million, and every read is a full table scan because nobody ever added an index. Adding the right index cut the query to 2 milliseconds in one deployment. Adding the wrong one did nothing except slow down every write. That is the entire story of database indexing: a few bytes of metadata can be worth more than a terabyte of RAM, but only when placed deliberately.

An index is a separate, ordered data structure — usually a B-tree — that maps column values to disk locations so the database can find rows without scanning the table. Think of it as the card catalog in a library: the books stay unsorted on the shelves (the heap table), but the catalog tells you exactly which shelf holds the book you want.

What an Index Actually Costs

Indexes are not free. Every index consumes storage, and every insert, update, and delete must maintain every index on the table, adding latency to writes. The trade-off is read speed now against write overhead forever. A table with five indexes can make inserts 20-40% slower than a table with none, depending on your database and index types.

Because of this, indexes are a deliberate investment, not a default. The best indexes serve the queries your application actually runs repeatedly — the login check, the order history fetch, the deduplication lookup — rather than the columns that happen to be in your schema.

The Mechanisms: B-Trees, Hash, and Covering Indexes

Most database indexes are B-trees: balanced trees that keep data sorted and make point lookups and range scans fast at O(log n). A hash index, by contrast, is limited to exact-equality lookups and cannot do range queries, which is why B-trees dominate general-purpose databases.

A covering index contains all the columns a query needs, so the database answers the query entirely from the index without touching the table at all. This is the highest-leverage optimization in indexing: a covering index on (customer_id, order_date) can serve a "count my orders by month" query with zero table access.

How a Query Planner Uses an Index

You do not "use" an index; the query planner decides whether to use one. The planner estimates how many rows each strategy returns and picks the cheapest. It might ignore a perfect index if the column is wrapped in a function — WHERE EXTRACT(YEAR FROM order_date) = 2026 cannot use an index on order_date, while WHERE order_date >= '2026-01-01' AND order_date < '2026-01-01' can. The cardinality rule also matters: if a query matches more than about 5-20% of a table's rows, the planner often skips the index because reading the whole table sequentially is cheaper.

This is why you should look at the query plan, not guess. Every major database shows the actual plan: EXPLAIN in PostgreSQL and MySQL, EXPLAIN QUERY PLAN in SQLite, and graphical plans in SQL Server's SSMS and Oracle's SQL Developer.

Indexing Decisions by Situation

The index you need depends on the query you are serving. Here is how the common situations map to index choices.

• Primary key lookups. Your primary key gets a unique index automatically. Never create a duplicate one.
• Foreign key joins. Columns referenced in JOIN and WHERE clauses on the child table should be indexed. This is the most commonly forgotten index in real schemas.
• Filtering on one column. A single-column index usually suffices for equality filters.
• Filtering on several columns. A composite index with the most selective column first beats several single-column indexes.
• Sorting and range. Indexed columns can serve ORDER BY and range scans without a sort step. Put equality columns first and range/sort columns second in a composite index.
• Only-index queries. Add the columns you SELECT into the index to make it covering and skip table access entirely.

A composite index on (status, created_at) is ideal for "find the 50 most recent pending orders," because equality on status narrows first and the range on created_at orders the result. Reversing the column order — (created_at, status) — would not help that query at all.

Why You Should Never "Index Everything"

New developers often add an index on every column they filter, then wonder why writes got slower and the database ballooned. This strategy triple-fails: it wastes storage, slows every write, and confuses the planner into picking suboptimal indexes. The discipline is to index based on observed query patterns, not on hypotheticals.

Read your database's slow-query log to find the queries that actually matter. If a query dominates your workload and is slow, index it. If a query runs once a night in a maintenance job, leave it alone. The wider skill set around crafting and reading efficient queries belongs to a dedicated SQL database course. The same philosophy applies at the schema level: thoughtful database design that normalizes and names clearly gives your indexes a clean foundation to build on.

Comparing How Major Databases Handle Indexing

Indexing is universal, but each database adds its own flavor. Knowing what your database gives you for free changes how you design.

The takeaway: your choice of database does not change the fundamentals of columns, selectivity, and covering indexes. It only changes the syntax and the helper tools you lean on.

A Defensible General-Purpose Indexing Recipe

If you are starting from an unindexed schema with real traffic, this order of operations gets the most value per index:

1. Index every foreign key column. This is non-negotiable and fixes most join slowness immediately.
2. Add composite indexes for your top 3-5 slowest hot queries, matching each query's equality and sort columns.
3. Make your most important hot queries covering by adding the SELECTed columns to those indexes.
4. Drop indexes nothing uses. Query catalogs or the planner's recommendations, then remove redundant single-column indexes that duplicate the prefix of a composite index.
5. Re-test reads and writes after every change, using the query plan plus wall-clock latency, not intuition.

This is the same cost-conscious, measurement-first mindset you apply to database performance tuning and to the routine upkeep covered under database management.

Detecting Index Problems With Real Data

Two metrics tell you whether your indexing is working. The first is sequential scan ratio per hot table: if a large table is still scanned on every read, either you missed an index or your selectivity is too low. The second is write latency: if writes degraded after you added indexes, too many indexes or oversized ones are the likely cause.

Look at your slow-query log weekly, not monthly. Slow queries that a new index would fix are cheap; compound problems that accumulate for a quarter are expensive. If your schema is still evolving heavily, note that indexes follow database design principles: both are easier to get right when you plan joins and access patterns up front rather than retrofitting.

Frequently Asked Questions

How do I know which column to put first in a composite index?

Put the most selective column first (the one with the most distinct values among filtered rows), and put equality columns before range or sort columns. This lets the B-tree narrow the result set fastest. Verify with a query plan; the planner's index-scan row estimate tells you if you got it right.

Why is my query ignoring an index I just created?

Three common causes: the column is wrapped in a function so the value is transformed at runtime, the query matches too large a fraction of rows for an index scan to be cheaper, or the statistics are stale. Cast to a sargable form, check the plan, and run ANALYZE/update statistics to refresh the planner's estimates.

Do indexes always speed up SELECT and slow down writes?

Mostly yes for writes, because each row change must update every index. Reads speed up only when the index beats a table scan; a covering index can even eliminate table access entirely. The net effect depends on your read/write ratio and query patterns, which is why you should measure rather than assume.

Can I have too many indexes, and how do I tell?

Yes. Symptoms are slower inserts, larger storage, and a planner spending more time choosing among indexes than executing. Use your database's unused-index view (e.g., pg_stat_all_indexes in PostgreSQL) and drop indexes with near-zero usage that are only the redundant prefix of a composite index.

What is the difference between clustered and nonclustered indexes?

A clustered index (InnoDB primary key, SQL Server's default table organization) physically reorders the table rows by the key, so there can be only one per table. A nonclustered index is a separate structure that stores a pointer back to the row. Which you use affects whether lookups need an extra row-by-id hop.