Best practice for multi-table caching

Jon Drukman jsd at cluttered.com
Wed Feb 23 16:52:46 PST 2005


SELECT a.*, b.*, c.*
FROM TOPIC a, BOARD b, MSGS c
WHERE c.MessageID = 1 AND a.TopicID = c.MessageID AND b.BoardID = c.BoardID

How to memcache this data?
- set_cache("MY_WACKY_QUERY_1", data) note: 1 is the message id

Now there are lots of places in the code which update these 3 tables 
(independantly of each other), so we need to del_cache("XXX_1") whenever 
any of these updates and inserts affect MSGS, TOPIC, or BOARD data for, 
or relating to, the message with id 1.

For example: say the msg with an id of 1 exists in a board with an id of 
4. And later someone edits board 4 and changes the title. Then someone 
comes along and requests the wacky query data stored in memcache under 
'MY_WACKY_QUERY_1' - well sir, they will be seeing the wrong board 
title. So, we edit the code where the board title is updated to delete 
"MY_WACKY_QUERY_x" where x = all msgs within that board. We do the same 
thing for TOPIC and any other place where topic, board, and msgs is 
updated. It works, but what a pain. Plus when joe-bob is done coding and 
johnny-boy comes along and decides to add some code, johnny boy might 
easily screw up and not even know to delete memcache keys when certain 
tables are updated... Heck, for that matter joe-bob might even forget 
himself if he has a memory like mine.

Is there an easy solution? I'm stumped... Any ideas?



More information about the memcached mailing list