Database Indexing

An index is an auxiliary data structure that maps column values to row locations, letting the database find matching rows quickly instead of doing a full table scan. The classic structure is the B-tree, which supports efficient equality and range lookups in logarithmic time. The trade-off: indexes consume storage and must be updated on every INSERT/UPDATE/DELETE, so they speed up reads but slow down writes. Over-indexing a write-heavy table hurts performance — you index the columns you actually query and filter on.

A composite (multi-column) index indexes several columns together, e.g. (last_name, first_name). Column order is critical because of the leftmost-prefix rule: the index can serve queries filtering on last_name, or last_name AND first_name, but not a query filtering only on first_name. So you order columns by how they are queried — most selective and most commonly filtered first, with equality columns before range columns. Designing the column order to match your query patterns is the key skill in composite indexing.

A covering index includes every column a particular query requires — both the columns it filters on and the columns it returns. When this happens, the database performs an index-only scan: it answers entirely from the index without touching the table's row storage (the heap), avoiding extra I/O. For example, an index on (user_id, status) covers SELECT status WHERE user_id = ?. Many databases let you add non-key "included" columns to an index specifically to make it covering. It is a powerful optimization for hot read queries.

The query planner chooses an execution strategy, and EXPLAIN (or EXPLAIN ANALYZE) reveals it: which indexes are used, the join order, estimated vs actual rows, and whether a costly sequential/full table scan is happening where you expected an index scan. Reading EXPLAIN output is the primary tool for diagnosing slow queries: a "Seq Scan" on a large filtered table usually signals a missing index, while an unused index you created points to a leftmost-prefix or type-mismatch problem. Always verify with real data, since the planner relies on table statistics.

Indexes are not free. Every write must update all relevant indexes, so a table with many indexes pays a write-amplification cost on every INSERT/UPDATE/DELETE. They also consume disk and memory (indexes are cached too), and overlapping/redundant indexes waste resources while giving the planner more options to evaluate. The discipline is to index for your actual query patterns, remove unused indexes (the planner can show which are never chosen), and prefer a few well-designed composite/covering indexes over many single-column ones. It is a read-speed vs write-cost balance.