SQLite Forum

Most efficient way to tell if any database content has changed
I'm interested in answering the question "has the contents of this database changed in any way" as efficiently as possible.

I want to do this for caching purposes - I'd like to be able to cache various things relating to an infrequently changed database but invalidate that cache if even a single row has been inserted or updated.

I've successfully used `PRAGMA schema_version` to invalidate a cache when the database schema changes, but now I'm looking to do it based on the file contents instead.

Options I'm considering:

- Calculating the MD5 hash of the file contents on disk, which runs pretty fast (but not fast enough to do it often)
- Adding triggers to every single database table which increment a counter in a dedicated `content_version` table any time those tables have content inserted/updated/deleted

I'm pretty sure that second option would work correctly (I'm not at all worried about the additional overhead on writes), but I'm wondering if there's a solution I haven't considered that would be even better.