Effect of Index Design on Query Latency, Storage Overhead, and Write Throughput in SQLite: An Empirical Benchmark

Secondary indexes are the main tool for speeding up relational queries, but every index also costs storage and slows down writes. This study measures that trade-off in SQLite 3.45.1 using a synthetic orders table of 10,000, 100,000, and 1,000,000 rows. Three index configurations were compared (no secondary index, single-column indexes, and composite or covering indexes) across five query types: point lookup, range aggregation, multi-predicate filtering, a join with grouping, and a top-k query. Without secondary indexes every query was a full table scan, and latency grew roughly in proportion to table size; at one million rows the point lookup took 46.1 ms. Single-column indexes cut this to 0.04 ms, a speedup of about 1,155 times, yet they improved the 30-day range aggregation by only 1.3 times. Composite and covering indexes improved the same query by 26.6 times and the multi-predicate query by 127 times. The join query gained 3.6 to 4.1 times under both indexed configurations. These gains came at a price: at one million rows the database grew from 34.0 MB to 71.9 MB and 95.7 MB, and sustained insert throughput fell from about 673,000 rows/s to 34,000 and 17,000 rows/s. The results show that index benefit depends on how well an index matches a query's predicates and projected columns, not merely on whether an index exists, and that write-heavy workloads need to weigh these costs carefully.