What an index is (and what problem it solves)

Why indexes matter

A database can answer a query by checking every row (a full scan) or by using extra structures that let it skip most rows. Indexes are those extra structures.

Understanding indexes changes how you read query performance: you start seeing queries as “how many rows must be touched?” rather than “how fast is my server?” It also helps you make better schema and query choices—especially for large tables where the difference between scanning millions of rows and touching a few dozen is the difference between seconds and milliseconds.

The core idea: reduce the amount of work

A table is stored on disk/SSD in pages (blocks). If you run:

SELECT * FROM orders WHERE customer_id = 42;

Without an index on customer_id, the database may need to read many pages to check each row: O(N) work.

With an index, the database can do something closer to O(log N) (or O(1) in some cases) to find where matching rows live, then fetch only those rows.

The major areas you’ll see in index discussions

• Index structure: most commonly B-tree (ordered, good for ranges), sometimes hash (good for equality).
• What the index stores: the indexed key plus a pointer/row locator to the actual row (or sometimes the row itself, depending on storage).
• How it’s used in a plan: an index seek (targeted) vs index scan (walk many index entries) vs table scan.

Indexes speed up queries mainly by turning “look everywhere” into “go directly to the right neighborhood, then the right address.”