We used 50,000 real NYC 311 records to investigate the database work behind a service dashboard. Two targeted indexes cut query time in our local experiment; result checks and storage measurements show the tradeoff.
Unique request identifiers, with six selected fields and no customer names or addresses. [1][2]
Baseline 14.55 ms; same 50 result rows for the timed Brooklyn / NYPD request. [1]
Two queries × 71 observed borough–agency combinations; every result matched. [1]
Both indexes plus planner statistics increased the database from 4,829,184 to 10,272,768 bytes. [1]
A slow report is often a question about work done for each answer—not simply a shortage of servers.
SQLite 3.42.0 through Python 3.11.5 on Windows 10. The dataset is the first 50,000 unique keys in ascending order among records created in January 2025; its actual timestamps cover 1–5 January. This is a reproducible workload sample, not a representative sample of the month. We did not test NYC’s production database, a hosted application or Datasette’s HTTP layer.
Save the source response and its SHA-256 hash. Create two databases with identical rows and the same primary key. Fix the timed filter to Brooklyn / NYPD and keep query text, selected columns and result ordering identical.
Inspect both query plans. The baseline scans the table and sorts. Add one index for the recent-request list and one for complaint-category counts, then collect planner statistics with ANALYZE.
Warm each query five times, then run 40 measurements per variant while alternating their execution order. Include fetching all rows in the elapsed time. Compare results across all 71 observed filter combinations; use a request identifier to break timestamp ties.
Record median and nearest-rank p95 latency, database bytes and raw timing samples. Count SQLite virtual-machine instructions in separate runs so the counting callback does not distort the latency measurements.
For recent requests, the baseline plan reports a table scan and a temporary sort. The indexed plan uses a covering index: it can answer from the index itself. The separately measured instruction count falls from 208,717 to 361. The query and returned records are unchanged. [1][3][4]
Why it matters. This makes the result more useful than an isolated speed score: the access path explains which work was removed.
The category-count query needs a different ordering of index columns. Its instructions fall from 231,257 to 42,881, but its plan still sorts the aggregated counts. The indexed median is 2.48 ms; we do not describe that remaining sort as eliminated. [1][3]
Why it matters. Index design follows individual access patterns. Adding indexes indiscriminately would hide storage and write costs.
The database becomes 5,443,584 bytes larger. We did not measure insert throughput, production concurrency, CPU billing or an Azure invoice. A faster read is evidence of lower query work here; it is not evidence of a percentage reduction in operating cost. [1]
Why it matters. A cost recommendation needs the read/write mix, workload frequency and hosting measurements before resizing anything.
Baseline: primary key only. Median of 40 warm runs per query.
| Query | Baseline median / p95 | Indexed median / p95 | Result check |
|---|---|---|---|
| Recent 50 requests | 14.55 / 19.86 ms | 0.52 / 0.68 ms | 71 of 71 combinations match |
| Category counts | 17.08 / 37.18 ms | 2.48 / 3.78 ms | 71 of 71 combinations match |
40 warm measurements per query and variant. p95 is the 38th ordered observation, not a service-level objective. These are database-call timings on one machine, not website response times. [1]
The evidence download includes the exact source response, all raw timings, query plans and the analysis script. Run python scripts/analyze-public-studies.py from the extracted evidence folder. It uses Python’s standard library; SQLite versions are recorded in the output. Re-running refreshes local timing results, so compare the direction and query plans rather than expecting identical milliseconds.
SELECT unique_key, created_date, complaint_type
FROM requests
WHERE borough = ? AND agency = ?
ORDER BY created_date DESC, unique_key DESC
LIMIT 50;
CREATE INDEX requests_recent ON requests
(borough, agency, created_date DESC, unique_key DESC, complaint_type);
CREATE INDEX requests_categories ON requests
(borough, agency, complaint_type);These are SystemVale’s recommendations for a comparable product or service. They are not claims that the organisations studied implemented them.
Proposed for a comparable dashboard: capture its slow query and plan, add the narrowest index that supports the filter and ordering, and preserve a deterministic tie-breaker. Do not copy these indexes into a different schema without checking its actual access patterns.
Before rollout, measure insert/update latency, index build time and disk headroom on a realistic copy. If the workload is write-heavy or rarely uses the query, the additional index may not be worth keeping.
Repeat the measurement with the application, network and concurrent users included. On an Azure deployment, correlate query work with database CPU, request latency and provisioned capacity. Azure savings remain unmeasured in this study.
Record the index definitions, deploy through the normal migration process, observe the agreed workload window, and remove the added indexes if write regressions outweigh read gains. Never change the result contract to improve a benchmark.
Improve the access path before buying more capacity, and carry correctness and storage into the same decision.
Our interpretation of this study. Not a client quote or endorsement.
Query plans expose unnecessary scans and sorts. They turn a general complaint about speed into a change that can be tested.
Performance work is incomplete if faster queries return different rows, counts or ordering.
Read speed, write cost, storage and deployment risk belong together. A single latency number cannot settle the business decision.
Download the five-study evidence bundle: captured public data, source hashes, scripts, licenses and original result files. The README explains the commands and the boundaries of each experiment.
Measured 12 October 2026; local experiment.
Public source dataset; exact selection URL and SHA-256 are included in the evidence download.
Primary reference for multi-column and covering indexes; reviewed 12 October 2026.
Primary reference for interpreting scan, search and temporary sorting operations.