How to force mysql to cache a query for a specific period of time ?

Question

Hi all, is there a way to force mysql to cache a particular query, for say 5 minutes, so executing that query will always return the same result even if the underlying db changes ?

i'm running a dating site and i have a page that shows "newest matches" and it's hitting the db too much.

thanks in advance.

Answer 1

Here's a good tutorial to use:

http://www.devshed.com/c/a/PHP/Output-Caching-with-PHP/1/

Answer 2

Caching in MySQL is not a good solution to this problem.

MySQL automatically caches a query until the table is updated.

For your problem, you need to use a cache in front of the database. You can then check the cache first, and if what you are looking for is not in the cache hit the database and add it to the cache.

Memcached is a good solution for this, especially if you have more than 1 web server.

APC is another good solution that can also cache your PHP bytecode, but it is a local cache on a single machine only.