Chapter 14 · Indexes and Query Execution
Index Tradeoffs and Evidence-Based Optimization
Every index accelerates some access paths by adding another structure that must be stored and maintained. Production tuning is therefore a measured portfolio decision, not a contest to create the most indexes.
Learning outcomes
Tune a workload, not a screenshot
Quantify index benefits and costs across reads, writes, storage, and operational complexity.
Identify redundant, unused, over-wide, and low-value indexes.
Design an evidence-based optimization experiment with representative parameters.
Create and verify a composite covering index for a concrete reporting query.
Build a repeatable index review and retirement process.
The index cost equation
An index is valuable when its workload benefit exceeds its lifecycle cost:
ΔR is read benefit; ΔW is added write work; ΔS is storage; ΔM is maintenance; and ΔO is operational risk and complexity.
Latency and throughput
Fewer pages, less sorting, fewer row fetches, and better join access.
Amplification
Every qualifying insert, delete, or indexed-key update changes another structure.
Cache pressure
Wide or numerous indexes consume storage and displace useful pages from memory.
Maintenance
Statistics, rebuilds, corruption checks, backups, replication, and schema deployment all grow.
Plan change
A new index can improve one query while changing another plan unexpectedly.
Establish the baseline
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS sales_order;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE, region TEXT NOT NULL CHECK (region IN ('north','south','east','west')), joined_at TEXT NOT NULL) STRICT;CREATE TABLE sales_order ( order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), status TEXT NOT NULL CHECK (status IN ('pending','processing','paid','cancelled')), ordered_at TEXT NOT NULL, total_cents INTEGER NOT NULL CHECK (total_cents >= 0), channel TEXT NOT NULL CHECK (channel IN ('web','mobile','partner'))) STRICT;WITH RECURSIVE seq(n) AS ( VALUES (1) UNION ALL SELECT n + 1 FROM seq WHERE n < 1000)INSERT INTO customer (customer_id, email, region, joined_at)SELECT n, printf('customer%04d@example.com', n), CASE n % 4 WHEN 0 THEN 'north' WHEN 1 THEN 'south' WHEN 2 THEN 'east' ELSE 'west' END, date('2023-01-01', printf('+%d days', n % 730))FROM seq;WITH RECURSIVE seq(n) AS ( VALUES (1) UNION ALL SELECT n + 1 FROM seq WHERE n < 20000)INSERT INTO sales_order (order_id, customer_id, status, ordered_at, total_cents, channel)SELECT n, ((n * 37) % 1000) + 1, CASE n % 20 WHEN 0 THEN 'pending' WHEN 1 THEN 'processing' WHEN 2 THEN 'cancelled' ELSE 'paid' END, datetime('2025-01-01', printf('+%d hours', n % 8760)), 1000 + ((n * 7919) % 90000), CASE n % 3 WHEN 0 THEN 'web' WHEN 1 THEN 'mobile' ELSE 'partner' ENDFROM seq;CREATE INDEX IF NOT EXISTS idx_order_customer_dateON sales_order (customer_id, ordered_at);ANALYZE;SELECT order_id, ordered_at, status, total_cents, channelFROM sales_orderWHERE customer_id = 417 AND status IN ('paid', 'pending') AND ordered_at >= '2025-04-01'ORDER BY ordered_at DESCLIMIT 25;The baseline query has equality, a small status set, a date range, descending order, and five output columns. That complete shape—not one isolated predicate—drives the index design.
Design one candidate index
CREATE INDEX idx_order_customer_status_date_coverON sales_order ( customer_id, status, ordered_at DESC, total_cents, channel);ANALYZE;EXPLAIN QUERY PLANSELECT order_id, ordered_at, status, total_cents, channelFROM sales_orderWHERE customer_id = 417 AND status IN ('paid', 'pending') AND ordered_at >= '2025-04-01'ORDER BY ordered_at DESCLIMIT 25;Because order_id is the rowid alias in this SQLite table, it is also available through the ordinary index entry. The plan can be covering, although the IN predicate may still require merge or sort work depending on planner strategy.
Measure with representative parameters
| Bad benchmark habit | Better experiment |
|---|---|
| One hand-picked fast parameter | Use hot, cold, common, rare, empty, and boundary-value parameter sets. |
| Only warm-cache timing | Separate cold-start, warm-cache, and sustained-concurrency behavior. |
| Average latency only | Record median, tail percentiles, rows returned, pages/buffers, and variance. |
| Read test without writes | Measure insert/update/delete throughput and transaction latency after adding the index. |
| Different data for before and after | Use the same snapshot, statistics policy, and query text. |
| One run | Repeat enough times to distinguish signal from noise. |
Query fingerprint: customer history dashboardData snapshot: production-like 20k-order laboratoryParameters: common, rare, empty, and boundary casesBaseline plan: capturedCandidate change: one composite covering indexRead metrics: latency p50/p95, rows, sort, pagesWrite metrics: insert and update latencyDecision: keep, revise, or reject with evidenceInspect the existing index portfolio
SELECT tbl_name, name AS index_name, sql AS definitionFROM sqlite_schemaWHERE type = 'index' AND sql IS NOT NULLORDER BY tbl_name, name;PRAGMA index_list('sales_order');PRAGMA index_xinfo('idx_order_customer_status_date_cover');Map each index to owners and query fingerprints. An index without a known workload, constraint, or operational purpose is a review candidate—not an automatic deletion candidate.
Common index anti-patterns
| Anti-pattern | Why it hurts | Review question |
|---|---|---|
| Index every column | High write and storage cost with little workload alignment. | Which exact queries use each leading key? |
| Duplicate prefix indexes | Multiple structures may serve the same seeks. | Does a wider composite index already cover this prefix? |
| Very wide covering index | Poor cache density and expensive updates. | Are all payload columns needed often enough? |
| Low-cardinality standalone index | May return too much of the table. | Does it combine with a selective key or cover a critical query? |
| Unused specialized index | Ongoing maintenance without observed reads. | Was the observation window representative, including seasonal jobs? |
| Index created to hide bad SQL | Complexity remains and future predicates still fail. | Can the query or data model be corrected first? |
Write amplification laboratory
-- Review the plans and timing in separate clean database copies.BEGIN;WITH RECURSIVE seq(n) AS ( VALUES (20001) UNION ALL SELECT n + 1 FROM seq WHERE n < 21000)INSERT INTO sales_order (order_id, customer_id, status, ordered_at, total_cents, channel)SELECT n, ((n * 37) % 1000) + 1, 'paid', datetime('2026-01-01', printf('+%d minutes', n - 20001)), 5000 + (n % 50000), 'web'FROM seq;ROLLBACK;Run the same insertion against a minimal-index copy and a candidate-index copy. The rollback keeps the logical dataset stable, but the benchmark environment should still account for journal, WAL, cache, and checkpoint effects.
Retire indexes safely
Long enough window
Include month-end, seasonal, administrative, and disaster-recovery workloads.
Constraints and replicas
Confirm the index is not enforcing UNIQUE and is not required by downstream systems.
Plan without it
Use a staging clone, invisible-index feature where supported, or controlled drop-and-restore test.
Operational method
Prefer online or concurrent build/drop capabilities when the product supports them.
Rollback path
Keep the exact definition, creation procedure, expected build time, and monitoring thresholds.
CREATE INDEX CONCURRENTLY idx_order_customer_dateON sales_order (customer_id, ordered_at DESC);DROP INDEX CONCURRENTLY idx_order_redundant;SQLite schema changes use different locking and deployment behavior; test the exact application version, journal mode, transaction boundaries, and migration process.
Evidence-based decision record
Index: idx_order_customer_status_date_coverPurpose: customer-history dashboardBaseline: scan/search plus temporary sortCandidate plan: constrained covering searchRead result: p95 improvement recordedWrite result: insert overhead recordedStorage result: index size recordedRisk: deployment lock and plan regression reviewedDecision: keep only if total workload value is positiveReview date: scheduled after workload changesChapter 14 checkpoint
Approve or reject the index
- Why is a faster SELECT not enough evidence to keep an index?
- Which query details determine composite key order?
- Why must benchmarks include multiple parameter distributions?
- When can a seemingly redundant prefix index still be useful?
- What information should be preserved before dropping an index?
Review the answers
The index also affects writes, space, cache, maintenance, and other plans. Equality filters, range filters, join keys, ordering, and requested columns determine the candidate shape. Different parameters can produce different selectivity and plans. A narrower prefix index may differ in uniqueness, predicate, collation, size, or cache behavior. Preserve its exact definition, purpose, baseline evidence, recreation method, build time, and rollback thresholds.
Summary and references
- Optimize the workload’s total cost, not one query in isolation.
- Change one thing and compare controlled before/after evidence.
- Composite and covering indexes must reflect complete query shapes.
- Inventory, ownership, and review dates prevent index sprawl.
- Retire indexes through observation, simulation, controlled deployment, and rollback planning.
Chapter 15 continues with views and reusable query interfaces: ordinary views, materialized views, security boundaries, and maintainable database APIs.