Re: database caching in persistent handlers

[John ORourke <suppressed>]

Perrin Harkins wrote:

On Mon, 2007-01-15 at 13:02 -0600, Frank Wiles wrote:
  

   Not being a MySQL guy this could be fixed now, but last I heard
   MySQL tossed it's cache anytime the table was updated.  Not very
   efficient IMHO. 
    

Yes, modifying data in a table invalidates the cache for that table.
There's really no other way to do it.  If someone could create a cache
that correctly and efficiently invalidated specific rows, the database
vendors would have incorporated that into the core of their database
engines by now.
  

The thought process I've gone through corroborates this - at the most complex point I was looking at specifying a caching policy for each table - eg. (in a shop website) product info except stock levels can be cached for a day, static pages can be cached for ages, etc - but it always comes down to an efficient way of invalidating cached records.

The DB based caching is a good compromise between something which is simple enough to be fast and give a server-wide (process independent) improvement.  The only improvement would be adding another daemon which abstracts the DB and understands the application domain well enough to intelligently cache.

John