Strategies to cache from MySQL (or any DB I suppose)

[Perrin Harkins]

On Mon, 2005-11-07 at 09:46 -0800, mike wrote:
> MySQL query cache is a great idea... but I'm not sure how useful. The
> problem is, the same problem I see I think - they most likely do-table
> level caching because of how hard it would be to cache the resultsets
> for different types of queries mixing and matching columns that may or
> may not be affected by a write query.

I think you're misunderstanding how it works.  They do caching of result
sets for different types of queries, including ones that join tables.
However, they keep track of which tables each query depends on, and any
write to any of those tables will clear the query results from cache.

The only way you can do better than this is by deciding that you don't
care if some of your data is out-of-date for a certain period of time.
Preventing any out-of-date data would require parsing all the SQL and
knowing which rows it affected, at which point you have just written a
database and are probably now slower than MySQL.  We've been through
this discussion on this list before.

To make effective use of a cache, I suggest you decide which things can
be served stale, decide how stale they can be, and do your caching at a
higher level than queries if possible.

- Perrin