Best practice for multi-table caching
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