Indexes and transactions

How indexes make searches fast and what they cost, and how transactions keep data correct when changes go wrong or happen at the same time.

About 7 hours of study

Learning objectives

• Create indexes and read query plans
• Apply the leftmost prefix rule
• Use BEGIN, COMMIT, ROLLBACK and savepoints
• Explain ACID, isolation levels, locks and logs

Lessons

1. 1

   Indexes and how queries find rows

   A full table scan reads every row; an index, usually a B+ tree, finds rows in a few steps. CREATE INDEX, EXPLAIN QUERY PLAN, and the cost of every index.

2. 2

   Transactions and concurrency

   BEGIN, COMMIT and ROLLBACK; the ACID properties; savepoints; what goes wrong when transactions run at once, and how isolation levels, locks and logs prevent it.

Module practice

• Add the missing indexEasy
• What each index is sorted byMedium
• Which indexes can a city search use?Hard
• Try a delete, then take it backEasy
• Undo only the mistakeMedium
• A transfer that cannot go wrongHard

‹ Database designFinal assessment ›