Caching in PHP?

Question

We run a number of web applications written in PHP. Unfortunately there are some select queries within these with some pretty hefty joins which are causing MySQL to become less responsive.

Because of this we are looking into caching some of the regularly used joins. I have looked into Zend_Cache which looks promising, are there any other alternatives that may perform better?

Also what is the best back end for a cache? I believe Zend_Cache offers file based, Sqlite and Memcached.

Answer 1

Perhaps you could start by checking if MySQL Query Cache is turned on, and if not, enable it. This only requires configuration changes to MySQL, and you don't have to start rewriting or adding caching code to your application.

Do some performance tests, and if the query cache doesn't speed things up enough go look into the other suggestions.

Take a look at this blog post for some basic info on query cache: http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

Answer 2

To solve this issue, I would:

1. Profile the queries to check what is wrong (Index missing, MySQL not properly set).
2. Cache the output (Zend_cache, Cache_lite, memecached, etc.) if the database server is really overloaded.

For (1):

1. Learn to understand: EXPLAIN ....
2. Learn to understand: SHOW STATUS

That way, you will understand why your queries are slow.

Answer 3

Turn on qcache (Query Cache) in MySQL: http://www.databasejournal.com/features/mysql/article.php/3110171/MySQLs-Query-Cache.htm

Answer 4

PEAR's Cache_Lite package ( http://pear.php.net/package/Cache_Lite/docs ) is another caching solution which you might also want to evaluate. It only uses a file-based cache though.

You should of course analyse the queries that are proving to be slow and see whether you can do anything that might make them quicker.

Answer 5

If you have only one webserver and don't need to distribute the cache across multiple servers then you can use the APC backend for Zend_Cache. It is a lot faster than memcached.

Answer 6

Look at http://code.google.com/p/samstyle-php-framework/source/browse/trunk/inc/cache.inc.php. You can save the table (in a PHP array) into the cache, then retrieve it again later easily.