Cacheing common reports from a database inside the database itself is surprisingly easy and makes a huge difference for Ajax style web pages.

If I break the request in to midnight aligned 24 hour periods, then I can cache the result for any completed day. This way I only need to pull new days of data. 

This helps, but it turns out I don't revisit days often during a session.

Fail miserably. It turns out SQLite does my query faster without indicies, so I took them off completely. (Sequential read on my virtual server is much faster than random.)

Now we get to the meat. I can cache pre-compressed reports for each of my daily periods. There are a couple of wrinkles though. I need some way to invalidate a report when the underlying data changes. (Sometimes some observations can be delayed and trickle in later.) I can't think of a good way to have the database delete a cache file, so instead I store the cached copies in the database.

This turns out to be surprisingly clean to code, and a simple set of triggers on the underlying data can remove any affected report.

It would be more efficient to keep the cached reports as files, or better let in a httpd cache, but then they could not be invalidated by the database. 

I'll get to that.