Search code examples
sqldatabasesqlitequery-cache

Implementing a query cache in SQLite

Does SQLite have any notion of a query cache? For example, if I execute the same query two times in a row:

select * from Sales1m group by territory_id;
select * from Sales1m group by territory_id;

Result: 136 rows returned in 6663ms

Result: 136 rows returned in 6745ms

It takes just about the exact same time, even though PRAGMA cache_size=-1000 (1MB) is set.

Does the second query benefit at all from the first query? If the answer is no, how difficult would it be to implement the query-cache on client-side? I suppose a naive implementation could use the query-string checksum and the result-set (potentially compressed?), but then it'd also need to invalidate whenever the table(s) are updated.

Solution

  • AFAIK SQLite does not have a built-in query cache. You'd have to add one at the application layer, probably by using an ORM which provides a query cache.

    What it does do is cache database pages in memory for faster retrieval. You can adjust the size of this cache with the cache_size pragma. By default it is 2000 kibibytes (2 megs). Try something larger. For example, 20,000 kb (20 megs).

    PRAGMA <your schema>.cache_size = -20000