Caching Your Data With MySQL

I was working on a script today, and I was continually querying my stats page (it was a robot that would fetch my stats). So, it was querying the server every 5 minutes, and if my stats changed, it would notify me. Then, I was thinking, every 5 minutes, means 12 times an hour, 288 times everyday. Wow. That may be a little too much.

So, next, I had 2 choices. One which was cool, one that was simple. The simple one was obviously to delay the cron job that I've setup to run every 30 minutes, or every hour, I don't really care. The 2nd one was to cache the stats page with MySQL. Obviously, if I cached it, the stats wouldn't change, until the cache was marked "old" by my script, so it would be quite useless to do so.

Though, I decided to go for the caching way, just to see what I could make from it. And I came up with a simple, very simple setup. Which required like 10 lines of PHP code.

$sql = mysql_query("SELECT * FROM `stats` WHERE `site` = 'example' AND `lastupdate` > " . (time()-60*30));
// means that it would not find any rows when the time last updated is more then 30 minutes ago.
if($r = mysql_fetch_assoc($sql))
{
... do some code when the cache is fresh ...
}else{
... do some code when the cache is too old ...
... for example, fetch my stats, and add them to
the database, and don't forget to change the
"lastupdate" column ...
}

You get it? Like I said, for my stats script, it was pretty useless. But what if you want to display your stats to your visitors? Then it would change! Say you have 1 visitor every minute. For every visit, you would query the stats page from a website (it's a 3rd party stats software). This would mean 60 times every hour, or 1440 times every day! Wow!

Instead, you would now set a cache, load the page into MySQL, and process it from there. Saves bandwidth, saves server resources.

If you have any questions because you aren't sure how this works, please feel free to comment. Otherwise, feel free to comment too.