SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru Angry Coder's Avatar
    Join Date
    May 2002
    Location
    Canada
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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?!

  2. #2
    SitePoint Wizard bronze trophy
    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.

  3. #3
    SitePoint Guru Angry Coder's Avatar
    Join Date
    May 2002
    Location
    Canada
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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?!

  4. #4
    SitePoint Guru Angry Coder's Avatar
    Join Date
    May 2002
    Location
    Canada
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation

    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?!

  5. #5
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    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.

  6. #6
    SitePoint Guru Angry Coder's Avatar
    Join Date
    May 2002
    Location
    Canada
    Posts
    599
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Dan, I'll try that.
    Why It Doesn't Work?!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •