SQL / CAPSTONE PROJECTS
Project: tuning a slow dashboard query by query
Work a slow dashboard one tile at a time: rank queries by time, read the plan, and delete the step that touches rows the tile never needs.
What you will learn
- Rank tiles by measured time and tune the one query that dominates the page
- Read a plan for the step that scales with rows, not for the presence of an index
- Rewrite function-wrapped date filters as half-open ranges on the bare column
- Order composite index columns equality-first, then range, then covering columns
Understanding Project: tuning a slow dashboard query by query
A dashboard is not one workload, it is a dozen independent queries that happen to share a page. When someone says the dashboard is slow, one or two tiles almost always own most of the time, so the first move is to log each tile's SQL with its own wall time for a single page load and sort that list descending. Tuning the page as a whole means guessing which tile you helped; tuning the top entry means you can prove it.
For a single tile, the plan names the operation that grows with table size. Version A below reports SCAN orders: 24000 rows read so that 100 can be summed. The index on created_at exists, but substr(created_at, 1, 10) = '2026-03-01' hides the column behind a function call, and an index is only a sorted copy of the column's own values, so the engine has no way to relate that computed string to the ordering and must evaluate it row by row. Version B asks the identical question as a half-open range on the bare column, which is exactly what a sorted structure can answer: seek to the first entry at or after the start, walk forward, stop at the end.
Change one thing, then re-read the plan and re-run the numbers. A plan moving from SCAN to SEARCH proves the access path changed; it does not prove the tile still returns the same answer, which is why the two spellings get compared side by side. Indexes are also not free, since each one is maintained on every insert and update and only earns that cost for tiles whose filters or ordering it can serve, so stop adding them the moment the tile fits its time budget.
-- SQLite. One dashboard tile: revenue for a single day.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
created_at TEXT NOT NULL, -- 'YYYY-MM-DD HH:MM:SS'
status TEXT NOT NULL,
amount REAL NOT NULL
);
WITH RECURSIVE days(d) AS (SELECT 0 UNION ALL SELECT d + 1 FROM days WHERE d < 239),
nums(k) AS (SELECT 1 UNION ALL SELECT k + 1 FROM nums WHERE k < 100)
INSERT INTO orders (created_at, status, amount)
SELECT datetime('2026-01-01', '+' || d || ' days', '+' || (7 * k) || ' minutes'),
CASE WHEN k % 4 = 0 THEN 'cancelled' ELSE 'paid' END,
(k % 50) + 1
FROM days, nums; -- 240 days x 100 orders = 24000 rows
CREATE INDEX orders_created_at ON orders(created_at);
-- Version A: the filter most people write first.
EXPLAIN QUERY PLAN
SELECT sum(amount) FROM orders
WHERE substr(created_at, 1, 10) = '2026-03-01' AND status = 'paid';
-- Version B: same rows, filter restated as a half-open range on the bare column.
EXPLAIN QUERY PLAN
SELECT sum(amount) FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02' AND status = 'paid';A dashboard gets fast one query at a time: find the tile that dominates the page, find the single plan step that touches rows the tile does not need, and remove that step.
Worked examples
Bound the win before you tune
Compares the rows version A walks with the rows the tile actually reports on, which is the ceiling on any speedup.
SELECT (SELECT count(*) FROM orders) AS rows_version_a_walks,
(SELECT count(*) FROM orders
WHERE created_at >= '2026-03-01'
AND created_at < '2026-03-02') AS rows_the_tile_needs;Example explained
Line 1The first subquery counts exactly what SCAN orders has to read: every row in the table.
Line 2The second counts the day the tile reports on, one of the 240 days present.
Line 324000 / 100 = 240, so no index can buy more than a 240x reduction in rows touched here.
Line 4If the two numbers were close, indexing would be the wrong move and a stored daily total would be the answer.
Prove the rewrite returns the same tile
Runs both spellings of the filter in one result set so the plan change can be shown not to change the answer.
SELECT 'substr' AS variant, count(*) AS order_count, sum(amount) AS revenue
FROM orders
WHERE substr(created_at, 1, 10) = '2026-03-01' AND status = 'paid'
UNION ALL
SELECT 'range', count(*), sum(amount)
FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02' AND status = 'paid';Example explained
Line 1UNION ALL keeps both rows even when they are identical, which is the point of the check.
Line 2Both report 75 orders: 100 per day, minus the 25 the generator marked cancelled.
Line 3The range form is safe because 'YYYY-MM-DD HH:MM:SS' sorts as text in the same order as the instants it encodes.
Line 4Matching counts and sums are the evidence the rewrite is a tuning; the plan line alone never shows that.
One covering index for the whole tile
Shows the column order that lets a single index resolve the equality, the range, and the aggregate without reading a table row.
CREATE INDEX orders_status_created_at ON orders(status, created_at, amount);
EXPLAIN QUERY PLAN
SELECT sum(amount) FROM orders
WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02' AND status = 'paid';Example explained
Line 1status leads because it is an equality test: all 'paid' entries sit in one contiguous block, and created_at then sorts inside that block, so the day is a single slice.
Line 2Reversed, with created_at first, the seek could only use the date and status would be tested on every entry in the range.
Line 3amount is in the index purely so sum() never fetches a row, which is what COVERING in the plan line means.
Line 4The cost is three columns to maintain on every write to orders, paid back only by tiles that filter on status.
Important notes
Plan wording is version and engine specific: SQLite before 3.36 prints SCAN TABLE orders, some browser editors show the raw id/parent/notused/detail columns instead of the tree, and Postgres and MySQL use EXPLAIN ANALYZE with different vocabulary. Read the structure, which table and which access path over which index, not the exact string.
EXPLAIN QUERY PLAN does not execute the statement, and SQLite's planner works from built-in guesses until ANALYZE has been run, so a plan that reads well can still be slow. Keep timing the real query.
Common mistakes
Creating one index per column named in the WHERE clause: a query normally uses one index per table, so the extras are never opened while every INSERT pays to maintain all of them.
Indexing created_at while the tile still filters substr(created_at, 1, 10) or strftime('%Y-%m-%d', created_at): the plan stays SCAN and the reader concludes that indexes do not help.
Fixing the range with BETWEEN '2026-03-01' AND '2026-03-02': the upper endpoint is inclusive, so an order stamped exactly 2026-03-02 00:00:00 lands in both days and daily revenue stops adding up to the total.
Try it yourself
Change, predict, then run
On the same table, add a tile that counts orders by status for March 2026: write the filter first as strftime('%Y-%m', created_at) = '2026-03' with GROUP BY status and look at its plan, then restate it as created_at >= '2026-03-01' AND created_at < '2026-04-01'. Confirm the plan changes while the counts stay 2325 paid and 775 cancelled.
Open the SQL workspaceCheck your understanding
A tile's plan reads SEARCH orders USING INDEX orders_created_at (created_at>? AND created_at<?), yet it is still one of the slowest queries on the page. What is the most likely explanation?
- The requested range covers a large share of the table, so the search still walks most index entries and fetches a table row for each match.
- SEARCH is what SQLite prints when no usable index exists, so the index is being ignored.
- The index is fragmented, and SEARCH means it must be rebuilt with REINDEX before it can help.
- Indexes can accelerate count(*) but never sum(), so aggregate tiles cannot be indexed.
Show answer
SEARCH only says the engine can seek straight to the first matching entry; the payoff is proportional to how narrow the slice is. One day out of 240 is 1/240 of the table, a three-month range is a quarter of it, and each match in a non-covering index adds a separate row fetch. Option 3 is tempting because the tile aggregates, but sum() benefits exactly like count(*), and putting amount in the index lets sum() finish without touching the table at all.