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

[mike]

On 11/7/05, Jacob Coby <jcoby at listingbook.com> wrote:

> Well, if you use postgresql, there is the pgmemcache extension.  I don't
> know how easy mysql is to extend (or have it call an external process in
> a trigger), but you may be able to write something similar?
>
> http://people.freebsd.org/~seanc/pgmemcache/

Yeah, I was thinking, external calls based on some trigger would have
been a way to handle it. I don't think MySQL still supports that even
in 5.x.

On 11/7/05, Paul G <paul-lists at perforge.com> wrote:

> mysql already does query caching, from 4.something and up i believe. the
> catch there is that a write to said table invalidates the cache. depending
> on your usage pattern, this can be hacked around by sticking updates into a
> second table with the same schema, performing queries against both and
> flushing to the real table at an interval. this is still inferior to using
> memcached of course, but comparable to pgmemcache with no mucking about
> required.

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.

Even Brad says on the memcache main page:

"MySQL's query cache destroys the entire cache for a given table
whenever that table is changed. On a high-traffic site with updates
happening many times per second, this makes the the cache practically
worthless. In fact, it's often harmful to have it on, since there's a
overhead to maintain the cache."

"If the data you need to cache is small and you do infrequent updates,
MySQL's query caching should work for you. If not, use memcached."

Now it'd be great if MySQL 5 put more emphasis on this to make it more
robust, but this isn't the case right now. I haven't seen anything in
the documentation saying it's been updated.