Relational databases & indexes

A relational database keeps data in tables — rows with the same named columns — and links tables by keys, so one fact is stored once and SQL joins rows back together when you read. Without an index, finding a row means scanning the whole table; a B-tree index keeps the column sorted, so the database jumps to the row in a few page reads, even among millions. Indexes are not free — every insert, update and delete must also update each index, and each one takes disk space. A multi-column index helps only queries that use its leftmost columns, and EXPLAIN shows whether a query really uses the index or scans.

Use when

Data with clear relationships that many parts of the app read in different ways · Updates that must change several rows together, all or nothing — like moving money between balances · Queries you can't fully predict up front — SQL can ask new questions of the same tables

Avoid when

Indexing a column that is rarely searched but often written — the index costs more than it saves · Indexing a tiny table — scanning a page or two is already as fast as the index · Joining many large tables on every request at high traffic — measure it, or shape the data for that read

Trade-offs

Each index makes some reads much faster and every write a little slower · Normalized tables store each fact once, but reads pay for joining them back together · A wide, multi-column index serves more queries but takes more space and more write work

Requires

Databases: the system of record