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.