The Slow Dashboard Query
A school district dashboard shows average test scores by school and grade. The source table has 4 million rows: one per student per test, with columns student_id, school_id, grade, subject, score, and test_date. The dashboard runs a query that groups by school, grade, and subject, filters to the last 12 months, and computes average and count. It takes 45 seconds and times out for some users. The table has no indexes beyond the primary key. Each page load re-runs the query. There are about 300 schools and 12 grades. A developer proposes adding an index on every column and caching nothing, arguing 'indexes make queries fast'.
1. Explain why the query is slow and evaluate the 'index every column' proposal.
Read the worked answer
The query scans 4 million rows, filters on test_date, groups by three columns, and aggregates two values. With only a primary-key index, the database must read the whole table (or a large fraction of it) and sort or hash 4 million rows to group them. 45 seconds is consistent with a full scan plus aggregation at that size. Re-running on every page load multiplies the cost by the number of users.
Indexing every column is a poor fix:
- A single-column index on school_id, grade, or subject has low selectivity (300 schools, 12 grades) and the planner will often ignore it for a query that touches most rows.
- An index on score is useless for filtering and adds write cost.
- Each index consumes storage and slows inserts and updates; with six or more indexes the write path degrades and maintenance grows.
- The real cost is the scan and aggregation, which a per-column index does not remove.
Better approach:
- A composite index on (test_date, school_id, grade, subject) -- or a covering index including score -- lets the database filter by date and group without touching the base table, often a large speedup.
- Pre-aggregate: compute daily or weekly rollups (school, grade, subject, date, sum, count) and have the dashboard average the rollups. The result set is tiny (300 x 12 x subjects x days) and queries become milliseconds.
- Cache the rendered result for a short TTL, since the underlying data changes slowly.
- Only then consider indexes, chosen from query plans, not by default.
Measure with EXPLAIN before and after; do not assume the index is used.
2. The district wants the dashboard to reflect new test results within one hour. How would you design the refresh, and what accuracy trade-offs does it introduce?
Read the worked answer
Design: an incremental rollup pipeline. When new test rows arrive, aggregate them into the rollup table keyed by (school, grade, subject, date) using upserts that add to sum and count. Run this on a schedule (e.g. every 15 minutes) or on insert, and have the dashboard read the rollup. This keeps freshness within the interval and makes queries cheap. Keep the raw table as the source of truth so rollups can be rebuilt.
Trade-offs:
- Staleness window: results are up to one interval old. With a 15-minute job, that is well within the one-hour requirement, with margin for job failure.
- Correctness under late or corrected data: if a score is edited or a test is backdated, a naive additive upsert will double-count or miss it. Handle corrections by recomputing the affected day's rollup from the raw table rather than adding deltas, or by tracking processed rows.
- Partial failure: a job that crashes midway can leave rollups inconsistent. Make each run idempotent (recompute the window) and record the last successful watermark.
- Time zones and the 'last 12 months' boundary: define the cutoff consistently in one time zone or results will shift at month edges.
- Small-cell suppression: if the dashboard shows averages, very small counts (e.g. 1-2 students) can leak individual scores. Suppress cells below a minimum count.
Validation: reconcile rollup totals against the raw table periodically, and alert if the job's watermark falls more than one interval behind. Document that the dashboard is near-real-time, not exact-to-the-second, and that corrections may take one cycle to appear.