liveBook · Manning

Database index

As explained in Grokking Software Architecture

An auxiliary database data structure that accelerates retrieval by organizing references to rows according to selected values.

A database index is a sorted lookup structure that helps the database find rows without checking every row. Like a phone book’s index, it points from a selected value to the matching data. A common index structure is a B-tree, which keeps values sorted and balanced as records change. section 8.5.1

An index trades faster lookups for extra work when data changes:

  • Without a suitable index, finding a user by email may require a full table scan; an index can let the database jump to the matching row. section 8.5.1
  • Indexes add a write penalty: INSERT, UPDATE, and DELETE operations must maintain them. Indexing every column can therefore slow data changes. section 8.5.1 section 8.10
  • Choose columns based on lookup needs; avoid over-indexing low-cardinality columns or massive text fields. section 8.10

Finding Bob

Searching for a user with email bob@aol.com can mean reading every row, like scanning a phone book cover to cover. With an index, the database looks up the email and jumps to the row instead. section 8.5.1

Figure 8.10 Indexing: An index allows the database to jump directly to the data instead of scanning every single row.

Shows how an index lets the database jump to a row instead of scanning every row. section 8.5.1

When performance matters, inspect the SQL and execution plan rather than treating the database as a black box. An index can help, but its read benefit must justify its write cost. section 6.8.3 section 8.5.1

How Grokking Software Architecture covers it

How deep it goes
Defines B-tree lookup behavior and read-write tradeoffs, then cautions against indexing every column.
Examples
Uses a Bob email search, a phone-book analogy, and filing papers in multiple folders.
What you can do after
Evaluate index choices by weighing lookup speed against write cost and avoiding low-cardinality or massive-text columns.
How it connects
Connects B-trees and full table scans to SQL queries and INSERT, UPDATE, and DELETE operations.

Also explained in

Compare how 3 books explain database index →

Related concepts

This explainer was generated by Manning's knowledge graph from the text of Grokking Software Architecture. The section links point to the parts of the book it draws on.