Caching table results for better performance... how?

Question

First of all, the website I run is hosted and I don't have access to be able to install anything interesting like memcached.

I have several web pages displaying HTML tables. The data for these HTML tables are generated using expensive and complex MySQL queries. I've optimized the queries as far as I can, and put indexes in place to improve performance. The problem is if I have high traffic to my site the MySQL server gets hammered, and struggles.

Interestingly - the data within the MySQL tables doesn't change very often. In fact it changes only after a certain 'event' that takes place every few weeks.

So what I have done now is this:

1. Save the HTML table once generated to a file
2. When the URL is accessed check the saved file if it exists
3. If the file is older than 1hr, run the query and save a new file, if not output the file

This ensures that for the vast majority of requests the page loads very fast, and the data can at most be 1hr old. For my purpose this isn't too bad.

What I would really like is to guarantee that if any data changes in the database, the cache file is deleted. This could be done by finding all scripts that do any change queries on the table and adding code to remove the cache file, but it's flimsy as all future changes need to also take care of this mechanism.

Is there an elegant way to do this?

I don't have anything but vanilla PHP and MySQL (recent versions) - I'd like to play with memcached, but I can't.

Answer 1

Ok - serious answer.

If you have any sort of database abstraction layer (hopefully you will), you could maintain a field in the database for the last time anything was updated, and manage that from a single point in your abstraction layer.

e.g. (pseudocode): On any update set last_updated.value = Time.now()

Then compare this to the time of the cached file at runtime to see if you need to re-query.

If you don't have an abstraction layer, create a wrapper function to any SQL update call that does this, and always use the wrapper function for any future functionality.