SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Jul 2004
    Location
    Brooklyn, NY
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What's an efficient way to track the view count?

    How do websites like YouTube track their video's video count?
    For instance, YouTube only refreshes the video views every several hours, but it obviously still runs an update query on every page load.

    I would like to have a "top 10 items of the day" list, as well as top 10 this week and top 10 this month - but how do I do this efficiently?

  2. #2
    SitePoint Evangelist hexburner's Avatar
    Join Date
    Jan 2007
    Location
    Belgium
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Depends on the approach you want.
    What do you understand as '1 view'?
    Imagine I'm a visitor, and I view a clip 3 times in a row because it's so hilarious, would this count for 3 views, or for 1 view?
    FOR SALE: 1 set of morals, never used, will sell cheap

  3. #3
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    perhaps they update one database, then synchronize with the other every few hours. I doubt that you'd have any trouble using live stats unless you have a very busy site.
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  4. #4
    SitePoint Addict
    Join Date
    Jul 2004
    Location
    Brooklyn, NY
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hexburner View Post
    Depends on the approach you want.
    What do you understand as '1 view'?
    Imagine I'm a visitor, and I view a clip 3 times in a row because it's so hilarious, would this count for 3 views, or for 1 view?
    Count it as 3 views - it's not worth the trouble of tracking IPs or cookies.

    Quote Originally Posted by wheeler View Post
    perhaps they update one database, then synchronize with the other every few hours. I doubt that you'd have any trouble using live stats unless you have a very busy site.
    I am running several larges sites on 1 server.
    But suppose you have a single database that (as you said) tracks views and another which synchronizes with the content - how do you check which are the most popular items of the day, week, and month?

  5. #5
    SitePoint Evangelist hexburner's Avatar
    Join Date
    Jan 2007
    Location
    Belgium
    Posts
    591
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by knopix View Post
    Count it as 3 views - it's not worth the trouble of tracking IPs or cookies.
    That's the easiest and quickest way indeed.

    Don't bother synchronizing databases yet, maybe when your website gets alot of traffic you might want to consider this, but just not yet.
    FOR SALE: 1 set of morals, never used, will sell cheap

  6. #6
    SitePoint Addict
    Join Date
    Jul 2004
    Location
    Brooklyn, NY
    Posts
    316
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hexburner View Post
    That's the easiest and quickest way indeed.

    Don't bother synchronizing databases yet, maybe when your website gets alot of traffic you might want to consider this, but just not yet.
    Okay, but it doesn't answer any of the aforementioned questions.

  7. #7
    SitePoint Wizard wheeler's Avatar
    Join Date
    Mar 2006
    Location
    Gold Coast, Australia
    Posts
    1,369
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by knopix View Post
    I am running several larges sites on 1 server.
    But suppose you have a single database that (as you said) tracks views and another which synchronizes with the content - how do you check which are the most popular items of the day, week, and month?
    I am only theorizing here, but I would imagine if you have a database dedicated to counting views, then you would have a synchronization schedule - the data that you actively use for example on "today's most viewed videos" would just be whatever your normal database is telling you. You wouldn't retrieve it from the "stats" database, that would defeat the purpose.

    Quote Originally Posted by hexburner View Post
    That's the easiest and quickest way indeed.

    Don't bother synchronizing databases yet, maybe when your website gets ALOT of traffic you might want to consider this, but just not yet.
    Agreed, I doubt this is even worrying about, I don't think it would be a big deal to go
    Code MySQL:
    UPDATE video_views SET counter = counter + 1 WHERE id = $video_id"
    I have used similar code on a large site before without issue, combined with all the other database activity.
    Studiotime - Time Management for Web Developers
    to-do's, messages, invoicing, reporting - 30 day free trial!
    Thomas Multimedia Web Development

  8. #8
    SitePoint Evangelist praetor's Avatar
    Join Date
    Aug 2005
    Posts
    479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You create a table 'stats' with columns 'id_video, `date`, no_views ' with unique index on (id_video,`date`) .
    When somene views a video you do

    Code MySQL:
    insert into stats values(?,date(now()),1)  on duplicate key no_views=no_views+1;
     
    select id_video, no_views from stats where `date` = date(now()) order by no_views desc limit 10


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
  •