9. Indexes
1
The picture to keep in your head2
9.1 What is an index, exactly?3
9.2 What's the difference between a full table scan and an index lookup?4
9.3 What does an index look like inside?5
9.4 How do composite indexes work, and what is the leftmost-prefix rule?6
9.5 What is a covering index?7
9.6 How do you know whether a query uses an index?8
9.7 What does an index cost you?9
9.8 When does an index not help, or even hurt?10
Memory card and where the analogy breaks11
Practice: five levels12
Interview questions13
Solutions14
Sources15
Code lab9. Indexes
Memory card and where the analogy breaks
3 min read+10 XP
The memory card (tear this page out)
| # | Question | Memory code |
|---|---|---|
| 9.1 | What is an index? | A sorted copy that points to the page |
| 9.2 | Scan vs lookup? | Read every page, or jump to the page |
| 9.3 | Index structures? | Tree for ranges, hash for exact, LSM for floods of writes |
| 9.4 | Composite indexes? | Phone books find surnames, not first names |
| 9.5 | Covering index? | If the index has the answer, don't open the book |
| 9.6 | Is my index used? | Don't guess, EXPLAIN |
| 9.7 | The write cost? | Every index is a tax on every write |
| 9.8 | When indexes don't help? | Indexes help when you look up a few, by how the column starts |
The whole chapter in one line: Index the few columns your hot queries search, in the right order, check with EXPLAIN, and pay the write tax knowingly.
Where the analogy breaks
- A book's index is written once. A database index is rewritten on every change, thousands of times a second, which is why its write cost matters so much more.
- A reader decides whether to use the index. In a database, the planner decides for you, based on statistics that can be wrong.
- A book's index lists words. A database index can list combinations (user and date together), computed values (
lower(email)), and even carry extra columns so the table is never touched.
Quick check
Five questions on this chapter. Pick an answer, then check it.
quiz