On caching reports ... inside the database.
Cacheing common reports from a database inside the database itself is surprisingly easy and makes a huge difference for Ajax style web pages.
I have several systems that record data at regular intervals. For the sake of example let us consider a weather station which reports temperature, humidity, pressure, and wind every 10 minutes. If I want to graph this data for a three day period, I have to query out 432 of these rows and send them down to my browser. Unfortunately this is not fast enough.
Step 1: Get the browser cache working for me.
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.
Step 2: Tune the database indices.
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.)
Step 3: Server side cacheing. In the database.
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.
Step 4: Clean up the little PHP wrapper and stick it here.
I’ll get to that.