1

I am working on a video site. It's pretty heavy so far as traffic is concerned. I am trying to rank videos by the number of times videos are viewed. I.e. video A gets 5.2K views and B gets 5k views, so A ranks above B. But I want to calculate the views each video gets in a week (or a month, if needed) and then rank them by view counts.

I first created a table that contains views only. i.e. every time, a video is viewed a row is inserted into the table along with timestamp and video id. That was a horrible decision. Since the traffic was heavy, the table became huge, affecting performance. Is there is a better way to do this?

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
abhisek
  • 924
  • 1
  • 13
  • 27

2 Answers2

4

I would have the table storing the total number of views for a particular video against the week number. Just create/increment as needed.

i.e. have fields video_id, week_number, and total_views.

Have a good index (i.e. on week_number, total_views) so that the top ten is easy to extract.

Also have a cron job to delete old data (or archive it). That should keep the tables size manageable.

Ed Heal
  • 59,252
  • 17
  • 87
  • 127
1

To piggback on @Ed Heal's answer, summarization is definitely what you're looking for. However, designing a table that increments against the day or week of the year leads to problems when the interval turns over. As in, at the beginning of interval, everyone starts at 0. If you happen to be at the beginning of your interval, things look...odd.

I've found the most effective way to keep these statistics is use the trailing period. (ie for a week, you count views a video gets for the past seven days.) This shows a much accurate picture of how content on your site is trending. To do this, you need to run a cron job periodically that will calculate stats for each video in the trailing period desired. Store the summaries, and sort on those.

Chris Henry
  • 11,914
  • 3
  • 30
  • 31
  • You're right. At the beginning the chart for top 10 items looks odd. But actually I am going to make a chart for previous week. But thanks for your help! :-) – abhisek Apr 13 '12 at 15:20