Indexing & Transactions

How databases stay fast (indexes) and reliable (transactions).

What is an Index?

An index is a data structure (usually a B-Tree) that lets the database find rows without scanning the whole table — similar to a book's index letting you jump to a page instead of reading cover to cover.

Trade-offs of Indexing

Indexes speed up reads (SELECT) significantly, but slow down writes (INSERT/UPDATE/DELETE) slightly since the index must also be updated. Index columns you frequently filter or sort by.

Transactions & ACID

A transaction is a group of operations treated as a single unit — either all succeed or none do. ACID (Atomicity, Consistency, Isolation, Durability) guarantees this reliability even during crashes or concurrent access.

🌍 Real-World Use

A bank transfer (deducting from one account, adding to another) MUST be a transaction — if the server crashes after the deduction but before the addition, ACID guarantees ensure the whole operation rolls back instead of losing money.

💡 Pro Tip

Never index every column 'just in case' — over-indexing slows down every INSERT/UPDATE on that table. Only index columns actually used in WHERE, JOIN, or ORDER BY clauses.

🧪 Quick Self-Test

Check what you just learned — no pressure, just practice.

1. What data structure do most database indexes use?

2. What does the 'A' in ACID stand for?

← Back to all Notes