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.
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.
When speed is important you should defenatly go for memcached. But in most cases a filecache of sqlite cache serves your needs.
Zend_Cache is a very good Caching library, the fact that it support numerous caching mechanisms through it's adapter system makes changing caching solutions lateron a breeze, so i would defenatly go for Zend_Cache.
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/
Turn on qcache (Query Cache) in MySQL: http://www.databasejournal.com/features/mysql/article.php/3110171/MySQLs-Query-Cache.htm
It really depends on exactly what is causing these queries to be slow, and whether the queries are the same ones again, or if they're different every time.
Generally speaking, I'd initially focus on why the queries are slow and improving them. The cache used inside the database is efficient because the same cache entries can be used by different queries (assuming they use the same data).
Don't bother with the MySQL query cache, your database server's ram is better used increasing the innodb buffer pool (assuming you're using innodb). The main difficulty with the query cache is that every entry for that table gets thrown out when a single row in the table is modified - even if the results of that particular query had not changed.
The query cache can significantly harm performance if used incorrectly - but the innodb buffer pool is generally only beneficial.
If your data aren't too big, a cheap and low-risk solution is to buy your db server lots of ram and increase the innodb buffer pool to exceed the size of your data.
Client-side caches will be more scalable (for example, if one per web server) but less effective, and expose stale data to the application (they will probably not be automatically expired when their data become stale).
As with any performance thing, test, test test. Use production-grade hardware, production-like data and workloads.
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.
PEAR's Cache_Lite package ( http://pear.php.net/package/Cache%5FLite/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.
To solve this issue, I would:
For (1):
That way, you will understand why your queries are slow.