Lab / Open methodology
Database optimization: queries and indexes
200,000 orders summarized: 1.13 s → 7.19 ms. Synthetic lab; median of 10 runs. Open code and raw data.
Unindexed querytoSame query + composite index
SQLite 3.46 LAB MEASUREMENT
Find the rows you need without scanning the whole table.
Open order totals by customer. The same query before and after indexing, on the same SQLite engine.
Reports and list screens do less CPU work. Fixing the query plan can reduce the need to scale hardware.
Unindexed query
Scan the orders table for each customer total.
- CPU time
- 1.129 s
Same query + composite index
Use a covering index on customer, status and amount.
- CPU time
- 7.187 ms
Index + grouped-query control
An alternative grouped query was also measured for the same output.
- CPU time
- 10.16 ms
Bars start at zero and share the same time scale across implementations.
Add a composite covering index for the customer and status filters. Keep the grouped-query alternative as a separate control.
All load points and measurements
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed query | 5.995 ms | 5.996 ms |
| Same query + composite index | 0.146 ms | 0.147 ms |
| Index + grouped-query control | 0.213 ms | 0.215 ms |
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed query | 16.785 ms | 16.786 ms |
| Same query + composite index | 0.227 ms | 0.228 ms |
| Index + grouped-query control | 0.292 ms | 0.293 ms |
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed query | 55.749 ms | 55.743 ms |
| Same query + composite index | 0.536 ms | 0.538 ms |
| Index + grouped-query control | 0.694 ms | 0.695 ms |
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed query | 175.175 ms | 175.158 ms |
| Same query + composite index | 1.23 ms | 1.231 ms |
| Index + grouped-query control | 1.695 ms | 1.696 ms |
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed query | 345.649 ms | 345.617 ms |
| Same query + composite index | 2.292 ms | 2.298 ms |
| Index + grouped-query control | 3.264 ms | 3.265 ms |
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed query | 565.92 ms | 565.9 ms |
| Same query + composite index | 3.691 ms | 3.693 ms |
| Index + grouped-query control | 5.213 ms | 5.214 ms |
| Implementation | Median duration | CPU time |
|---|---|---|
| Unindexed query | 1.129 s | 1.129 s |
| Same query + composite index | 7.188 ms | 7.187 ms |
| Index + grouped-query control | 10.158 ms | 10.16 ms |
CPU cost at your workload
Compare the recorded CPU time at your own monthly volume.
One batch run = 200,000 Order records.
CPU time reduced / month3.1162 hours
Processing cost difference / month—Calculated after you enter a unit price.
Lab CPU time × your volume and unit price. This is not a hosting bill forecast; idle capacity, networking, storage, licences and migration are excluded.
Try it yourself
Replay the recorded lab results side by side.
As the workload grows
Duration by workload
- Before
- After
Duration by workload (ms)
Show as a table
| Order records | Before | After |
|---|---|---|
| 1,000 | 5.99 ms | 0.15 ms |
| 3,000 | 16.78 ms | 0.23 ms |
| 10,000 | 55.75 ms | 0.54 ms |
| 30,000 | 175.17 ms | 1.23 ms |
| 60,000 | 345.65 ms | 2.29 ms |
| 100,000 | 565.92 ms | 3.69 ms |
| 200,000 | 1,129.16 ms | 7.19 ms |
How we measured
Environment, protocol and technical analysis
Environment and data
- Environment
- Apple M3; Docker Linux arm64, 2-CPU quota, 1 GiB memory. PHP 8.4.26 / SQLite 3.46.1.
- Dataset
- 200 customers; 1,000–200,000 deterministic orders. All result rows and cent totals match across implementations.
- Tools
- PHP 8.4.26 · SQLite 3.46.1 · hrtime / getrusage · OPcache / JIT off · native arm64 Docker
- Repetitions
- 7 loads × 5 implementations × 10 repeats, plus 70 warmup observations. Every sample retained; rotating arm order.
Find the rows you need without scanning the whole table.
Open order totals by customer. The same query before and after indexing, on the same SQLite engine.
Identical PHP/SQLite versions, synthetic records and 200 customers. Two warmups then 10 runs per arm/size; every complete output matches an independent oracle. PHP lookup totals rebuilt each run. SQL timings are warm in-memory reads excluding loading/index creation. Index bytes and the single construction observation are separate. Primary 200,000-record point selected before measurement.
Inspect the measurement script ↗What changes for the business?
Reports and list screens do less CPU work. Fixing the query plan can reduce the need to scale hardware.
Change and scope
Add a composite covering index for the customer and status filters. Keep the grouped-query alternative as a separate control.
SQLite 3.46.1, warm in-memory data, 200 customers. Query timings exclude index construction and writes; the evidence includes index size and construction time. These are not results for other engines.
Index cost and the alternative query
At 200,000 records the index added 3,899,392 bytes; the single construction observation was 77.56 ms. Write performance is outside this read experiment. The indexed grouped-query alternative is retained in the table. Query plans show the change from scans to a covering-index search.
Where to start in your system
Identify the slow operation, data volume and peak workload. Establish its baseline, change the code and access pattern, then compare the same result.
What we did
Business rule and data
AIPrepared the synthetic workflow, tests and measurement automation.
VerificationPublished code and raw outputs are available for review; this is not a client project.
Implementation change
AIAdd a composite covering index for the customer and status filters. Keep the grouped-query alternative as a separate control.
VerificationOutput equivalence was independently checked; every repetition is retained.