We tried to develop a kinda-real-time statistics module to measure how hot/trend a content is. We know it is hard to achieve high end stuff like youtube/FB/twitter, our goal is simpler :
Count click/read/like/etc.. during the last hour/day/week/etc..
Calculate some basic indexes such as likeCount/clickCount, readCount/turnOverCount, etc...
Use the data in step 2 above for report or as input of other
We used mysql to log every user action on a content. After 1 month, the table got 10 billions rows when our website had around 2000 content and 1 million unique visitor a day. After 2 months, the query became unbearable slow :
Select count(*) from log_table where content_id = xxx and timestamp > (now - yyy)
Of course we have index on content_id and timestamp and the database size got very big.
I guess our solution is quite child-ish.. Is there a better algorithm to solve this? (How do Youtube/Twitter do to solve this in general) ?
P/S : I recently read about HyperLogLog, somehow understand it, can it help in this case ?