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

[Casper Langemeijer]

Hi Mike,

Have a look into MySQL HEAP tables. The HEAP storage engine allows 
you to store your tables into memory.

If you want to cache raw data from mysql, it's probably best to cache it 
inside mysql. You can still do queries. Ofcourse it's not as fast as 
memcached because of query processing.

Grtz, Casper

On Fri, 4 Nov 2005, mike wrote:

> Obviously it's quite easy to request a single row from a table based
> on a key of "table:PK" - however, what if I do not query the table
> based on it's PK?
> 
> I'd say the majority of the time, my highest traffic queries are not
> using a PK (or a compound PK) but a combination of [indexed] columns.
> Perhaps this is where I become "re-educated" in my thinking, and you
> guys say "hey stupid, instead of sending one database query to your
> database, break it up into two" - I *may* be over-engineering this.
> 
> Take for example my forum code.
> 
> User requests /forum/posts.php?t=$threadid
> 
> I select from my database:
> SELECT foo,bar,baz FROM posts WHERE threadid=$threadid
> 
> Obviously, when checking to see if the cache exists for that, I
> suppose I could use "posts:$threadid" for the key. But then I have to
> make sure that any time I may modify a row returned from that query, I
> have to dirty the cache for any already-cached information with
> threadid of $threadid.
> 
> Hopefully I'm making sense. Perhaps someone else has some BKMs on how
> to change/break up database operations so that I can do caching on
> nearly every type of SELECT I will use. Because I'd like to leverage
> memcache for everything possible.
> 
> File caching is easy - the key would always be the filename (or some
> sort of token based on it) - but the database stuff gets interesting
> based on how the data is being retrieved, and also how it's updated.
> 
> Thanks in advance,
> mike
>