Data & storage / 2 / 11
Database indexes
An order history page gets slow as the table grows to 40 million rows. We read the query plan, add the right B-tree indexes, and see what every index costs on writes.
- 13 min
- Intermediate
- 11 steps
- Page p95
- 2 400 ms
- Rows read per query
- 40 000 000
- Insert per row
- 0.3 ms
- Indexes on orders
- 1
- request
- reply
- redelivery
- duplicate
- lost
Numbers are a model scenario, not measurements
The real request
Orders API → Orders DBRequest
SELECT id, status, total FROM orders
WHERE customer_id = 421
ORDER BY created_at DESC LIMIT 20;2
Response
(20 rows)
Time: 2391.7 ms3
customer_id— The filter. Nothing tells the database where the rows of customer 42 are.LIMIT— We want only 20 rows. The database still has to find all candidates first.Time— 2.4 seconds for 20 rows. That is the time to read the whole table.
Course: 42 lessons
01 Foundations
02 Scaling
03 Data & storage
04 Communication
05 Reliability
06 Architecture
07 Security
08 Shipping
09 Whole systems
10 Business processes