Caching with PHP to take stress away from MySQL

Question

I'm writing a web service for my application and want to know the best way to handle the possibly tons of requests I might get. A lot of the data probably won't change throughout the day but the particular script I'm writing makes 3 MySQL queries which seem a little excessive considering the data will probably be the same as the last request to the script, and if it's not the same then it's no big deal.

Will performance be much better if I save the output XML/JSON to a file and then serve it to the requester throughout the day and then overwrite it with the first request of the following day? What's the best way of doing this?

I know Joomla and phpBB and other MySQL intensive applications use caching so as to not make as many MySQL queries, so this is what got me thinking.

EDIT - Forgot to mention I'm on Windows/IIS 7.0

Answer 1

You should take a look at memchached: access it through PHP's memcache or memcached

Memcache module provides handy procedural and object oriented interface to memcached, highly effective caching daemon, which was especially designed to decrease database load in dynamic web applications.

It is designed exactly for what you need and is used by many high-performance webapplications.

Memcached was developed to enhance the speed of LiveJournal.com, a site which was already doing 20 million+ dynamic page views per day for 1 million users with a bunch of webservers and a bunch of database servers. The introduction of memcached dropped the database load enormously.

Note
There are two (2) client libraries in PHP. Some more discussion on this can be found on serverfaul.com: memcache-vs-memcached and here is a comparison

Answer 2

The quick and dirty way would be something like this.

1. create a cachefilename for this specific url
2. check if such a file exists in the cache directory and if it is younger than n minutes

2.1 if not: 
2.2 // business logic goes here => Save output e.g. in a variable $output
2.3 save contents of $output in cachefilename
2.4 echo $output

3. if so:

3.1 $output = file_get_contents(cachefilename)
3.2 echo $output

It might not be as elegant as memcache or memcached, but you can use it virtually everywhere.

Answer 3

Memcached is great but might be a bit of an overkill for your intention. A file based caching approach like the one presented by Martin will work just fine. There is a number of ready-made caching libraries on file level for PHP out there.

Answer 4

Don't muck about with caching until you need to. Memcache is very often a premature optimisation and should be your last, not first, resort. Adding this sort of caching can result in complicated consistency problems.

Databases are not slow by nature, and certainly not slower than loading a bunch of cached data from flat files. But they can be slow by misuse. For example if one of your every-page-queries does a write to a MyISAM table, or does an unindexed query, or one of your queries is just complex and hard. Attack these situations by fixing your schema first.