An index changes read and write cost
Why searches can be expensive
Without a suitable index, a database may scan many rows to find matches. An index is an extra search structure over selected fields. It helps specific queries, not every operation automatically. Coverage here means the share of reads for which the chosen structure is actually useful.
What happens on a write
Adding or changing a row also requires updating its indexes. Faster reads therefore add write and storage work. Here, a read without an index costs 8 units and a write 1; each index adds 0.8 to a write. These are educational weights, not measured database milliseconds.
An example of the total work budget
At 600 operations/s there are 480 reads and 120 writes. Without an index, work is 480×8 + 120×1 = 3960 units/s, but only 1200 are available. One fully matching index lowers read cost to 1.5: total work is 480×1.5 + 120×1.8 = 936. More indexes without new useful access provide no benefit.
How to find a trade-off
First add one index and examine how well it matches queries. Then compare write cost and budget with multiple indexes. In a real database, queries and execution plans guide the choice; here we study the tradeoff without running a SQL engine.
Task
600 operations/s: 80% reads, 20% writes. Database budget: 1200 work units/s. Add an index matching the queries; extra indexes increase write maintenance and cost.
The simulated database performs 1,200 work units/s. Without an index, a read costs 8 and a write 1. With 100% coverage, an index lowers reads to 1.5; each index adds 0.8 to writes. Two identical indexes do not double query speed. Aggregate model, without actual SQL plans or disk I/O.
- Add an index from the palette.
- Adjust query coverage and compare read and write work.
- Check the solution, then try adding unnecessary indexes.