Last month I was getting high traffic on one of my sites and it was killing the server. Waiting times over 10 seconds (or timeouts) due to too many queries on mysql. So, I had to find a solution to stop quering mysql on every 'hit' on my site. This post will fill you in on how to use mysql query caching: easy, and fast with text files in a folder as cache. Let's start off with the example script (in PHP): show me! This is what it does: Step 1) We have a SQL query. Before we just ask mysql to get us that information, we check if a file exists (named after that sql query) in our "cache" folder. Step 2) If is does exist, we check how old the file is. - Over X days? Load the data from mysql, and update the file. - Under X days? Open the file, and print it so we don't need to load the server with a new query. Step 3) Oohh!! Wow, that's already it! So all you have to do to implement this: put your queries in a function. And create a directory "cache" that is writable (777). Load the mysql results and write them to a file. For a webserver opening a file (the cache) is faster then having to connect to mysql, and calculating the query. Look at the example above, and I think you are all done. note: this doesn't cache the whole page, only a small portion that needs data from the database. Update: This site is now served from an 64 bit Dual Core Opteron 265 - 2 GB server.
Posted by Yvo Schaap on .