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 ?

  • Does the brute force approach of putting it into a SQL database and querying it work? If so, start with that. – Philip Kendall May 5 '19 at 7:00
  • @PhilipKendall : i don't think so, it works but not sustainable after 2 months... – Ph H. An May 5 '19 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.