Most Viewed -- Today / This Week / All Time

[Angry Coder]

Hi,

Can anyone explain the concept behind calculating the videos that are most viewed 'Today' and 'This Week', just like YouTube. I know how to do the 'All Time' only

Please explain. Thanks.

[crmalibu]

Make a video_views table
video_id | view_date

Each time a video is viewed, a new record is inserted. Then you can easily count records in any time period.

[Angry Coder]

Hello,

Can you please confirm the solution suggested at the first reply is the best way to do this in MySQL:

http://www.sitepoint.com/forums/show....php?p=4172345

Your help would be greatly appreciated.

[Angry Coder]

Thanks but are you sure this is how big site like YouTube are doing it? Because that means YouTube's video_views table contains billions of records!

[Dan Grossman]

I've merged your two threads, we don't need to discuss the same issue in two places.

You do need to do more than keep a single count to be able to figure out the top viewed videos by day, week, or month, clearly. But you don't need to keep a row for every view the video has ever had.

YouTube probably does something in-between. Since the most fine-grained filtering they offer is "today", have a table with three columns (video_id, date, views). On each view of a video, update the views column of the row for that video and the current date.

For performance, the counts can be aggregated and cached as daily and weekly totals as columns in the videos table. You can then directly query the videos table without joining the views table and summing up the rows to figure out the "top today" or "top this week". Update the day's total throughout the day and you're all set.

And billions of records don't need to be kept. The past days' counts will never change, so you can safely dump the rows for dates older than you care about. In this case, no more than a week of view counts would have to be kept, so (number of videos * 7) rows in a small table.

[Angry Coder]

Thanks Dan, I'll try that.