Index

Picture a 1,000-page textbook. To find every mention of "caching" you could read all 1,000 pages, or you could flip to the index at the back, see "caching ... 212, 478, 690", and jump straight there. A database index works the same way. The table is the book, the index is the back-of-book listing, and the page numbers are pointers to where each row physically lives.

Without an index, a query like SELECT * FROM users WHERE email = 'a@b.com' forces a full table scan: the database reads every row and checks it. On a table with 10 million rows that means 10 million comparisons. With an index on email, the database walks a sorted structure and finds the matching row in roughly 23 steps instead. That is the difference between hundreds of milliseconds and under a millisecond.

An index is a separate copy of selected column data, kept in sorted order, with a pointer back to the full row. It is not the data itself, which is why you can add and drop indexes at any time without changing your actual records.

Most relational databases (PostgreSQL, MySQL/InnoDB, SQL Server, Oracle) build indexes as a B-tree, specifically a B+ tree. A B-tree keeps keys sorted and balanced so that the tree stays shallow even as it grows. A table with a billion rows still only needs about 4 to 5 levels of tree, so a lookup touches only a handful of disk pages. B-trees are good for equality (= 'x') and range queries (BETWEEN, >, <, ORDER BY) because the data is already sorted.

Some workloads use other index types. A hash index gives O(1) equality lookups but cannot do range queries. PostgreSQL offers GIN and GiST indexes for full-text search, JSON, and geospatial data. Many search and analytics systems use an inverted index, which maps each term to the list of documents that contain it.

Indexes can be single-column or composite (multiple columns). Order matters in a composite index: an index on (last_name, first_name) helps queries that filter by last_name, or by last_name and first_name together, but does not help a query that filters only by first_name. A covering index goes further by including every column the query needs, so the database answers the query from the index alone and never touches the table.

Add an index to columns you frequently filter, join, or sort on: foreign keys, email and username lookups, status columns used in dashboards, created_at for time-range queries. A primary key is automatically indexed. The payoff is largest on big tables with selective queries that return a small slice of rows.

Indexes are not free. Each one is a second structure the database must update on every INSERT, UPDATE, and DELETE, so writes get slower and storage grows. A table with ten indexes can take several times longer to write than the same table with one. Indexes that nobody uses are pure cost, so it is worth dropping them.

Indexes also stop helping in predictable cases. A low-cardinality column like a boolean is_active that is true for 90 percent of rows is a poor index target, because the database would still read most of the table. Functions on the column (WHERE LOWER(email) = ...) bypass a plain index unless you build a matching functional index. Always confirm an index is actually used by reading the query plan with EXPLAIN.

Suppose an e-commerce orders table has 50 million rows and a dashboard runs SELECT * FROM orders WHERE customer_id = 8123 ORDER BY created_at DESC LIMIT 20. With no index, every page load scans 50 million rows and the query takes seconds. After running CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC), the database jumps to the customer_id section, where rows are already sorted by created_at, and returns the latest 20 in a couple of milliseconds.

This is exactly how Postgres and MySQL power production apps. The composite order matters: customer_id first narrows to one customer, then created_at gives the sort for free, so the LIMIT 20 reads only 20 index entries. Run EXPLAIN ANALYZE before and after and you can watch the plan switch from Seq Scan to Index Scan.