Our application requires an activity log of actions that a user has done. For example:
3/19/2013
-------------
9:37 AM Chuck Norris scrapped 3 robots.
9:15 AM Chuck Norris fought 3 robots.
Currently this is done with a gigantic query that compiles all of a user's activity on a given day. The query is so slow that that application literally forbids you from looking at more than a single day's activity at a time. These results could be aggressively cached in Redis.
My question is: what is a good Redis key strategy for storing these activities?
I need to be able do a lookup primarily based on the user ID and a date range. I've played with the following possibilities, but nothing has really leaped out at me as the optimal solution.
-- psuedo-code, obviously --
HMSET user:5345:activity date "3/19/2013" time "9:37 AM" action "Some action"
LPUSH user:5345:activity (json)
LPUSH user:5345:activity:2013:03:19 (json)
All three of these have significant tradeoffs. Assume a user hasn't logged in for four months - they might actually want to see their activity from four months ago at the top of the list. The second option gives me that, but there are trade-offs in that direction as well (like not being able to specifically query by date).
Any thoughts on a better way to approach this?