TinkerCostliest request: one order by number, 200 pages each, 2,000 a day.200 pages read · 10,000 rows read
Chapter 7 of 7
Plan Larkspur’s indexes
Everything is on the bench: a day of requests, the new orders, five possible indexes and a budget for writes.
This chapter: Which indexes make the day’s reads cheapest within the write budget?
About this chapter
You will learn
- How to weigh reads against writes
- Why one index can serve two requests
- Why some indexes aren’t worth their writes
Words you’ll meet
- Workload
- The requests and writes a database serves in a day, with how often each comes.
- Write budget
- How many pages a day the team lets new orders cost, so checkout stays quick.
Step 1 of 2
The is the day’s requests and 300 new orders. The : 4,300 pages a day for writes.
Pick the best indexes.
528,800 pages read a day BEST IS 21,240
| Request | A day | Way | Pages a day |
|---|---|---|---|
| One order by number | 2,000 | Full scan | 400,000 |
| A customer’s orders | 400 | Full scan | 80,000 |
| A customer’s orders this month | 200 | Full scan | 40,000 |
| Today’s orders | 40 | Full scan | 8,000 |
| Returned orders | 4 | Full scan | 800 |
| New orders | 300 | Writes | 300 of 4,300 |
The order system may spend at most this many page writes a day on new orders, so checkout stays quick in the rush.
The SQL
-- no indexesA teaching database: Larkspur’s generated orders in a real B-tree, pages counted as it runs. Not any real database’s numbers.