SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
-
Mar 2, 2009, 20:37 #1
Most Viewed -- Today / This Week / All Time
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.Why It Doesn't Work?!
-
Mar 2, 2009, 21:01 #2
- Join Date
- Jul 2008
- Posts
- 5,757
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Mar 3, 2009, 00:03 #3
Can any MySQL guru confirm this?
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.Why It Doesn't Work?!
-
Mar 3, 2009, 00:06 #4
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!
Why It Doesn't Work?!
-
Mar 3, 2009, 00:50 #5
- Join Date
- Aug 2000
- Location
- Philadephia, PA
- Posts
- 20,578
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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.Try Improvely, your online marketing dashboard.
→ Conversion tracking, click fraud detection, A/B testing and more
-
Mar 3, 2009, 20:42 #6
Thanks Dan, I'll try that.
Why It Doesn't Work?!
Bookmarks