The Little Guy Posted October 30, 2008 Share Posted October 30, 2008 I am going to add a feature to my video page, that displays the number of times the video has been viewed. Would it be best to: a. Count all the rows found with that video ID using a COUNT. b. Give each video a field called "views", and place a grand total in it. Quote Link to comment Share on other sites More sharing options...
trq Posted October 30, 2008 Share Posted October 30, 2008 a. What exactly are you stroing in all these rows? I mean, are you adding a new record each time the vidio is viewed? b. Seems a good option unless you are creating the rows in a for some other reason. Quote Link to comment Share on other sites More sharing options...
Maq Posted October 30, 2008 Share Posted October 30, 2008 I would go with b. It is the easiest and fastest way to count the views instead of going through all the records, especially if your video collection expands. Every time someone views the video, just increment by 1 and you're done... Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted October 30, 2008 Author Share Posted October 30, 2008 a. The row would contain information about the user, such as: ip, date viewed, user id (if they are logged in) for sure. Maybe some other info too. If I went with option b. I would then use option a. as well, because I don't want to increment the count if the user refreshes the page, only if they come back 24 hours later. Quote Link to comment Share on other sites More sharing options...
Maq Posted October 30, 2008 Share Posted October 30, 2008 I don't want to increment the count if the user refreshes the page, only if they come back 24 hours later. You could assign a cookie that expires in 24 hours. Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted October 30, 2008 Author Share Posted October 30, 2008 the user can easily delete the cookie Quote Link to comment Share on other sites More sharing options...
Daniel0 Posted October 31, 2008 Share Posted October 31, 2008 They can also change IP, user agent, create new accounts, etc. Quote Link to comment Share on other sites More sharing options...
Mastodont Posted October 31, 2008 Share Posted October 31, 2008 How many people are used to play with cookies only to confuse some remote unimportant statistics? One per thousand? Quote Link to comment Share on other sites More sharing options...
dantro Posted October 31, 2008 Share Posted October 31, 2008 a good solution would be to have a log table for all video views. therefore you are free to analyze this data later on like views per hour etc. on a next step you build a cronjob which counts all video views and writes the sum into one table field (update col countviews in video table). Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted October 31, 2008 Author Share Posted October 31, 2008 a good solution would be to have a log table for all video views. therefore you are free to analyze this data later on like views per hour etc. This is what I was thinking... I wanted to do option a, because I currently have a table that has almost 9K rows in it, and it queries a count in less than one second. The problem is, will this take WAY too long when there is say 1M - 5M rows? on a next step you build a cronjob which counts all video views and writes the sum into one table field (update col countviews in video table). I would like to minimize the number of queries to do this, and the only way I know of would be to make a loop, that does a query every single pass, and if I have 20,000 videos, this would do around 40,000 queries, not cool. Quote Link to comment Share on other sites More sharing options...
Mastodont Posted October 31, 2008 Share Posted October 31, 2008 The Little Guy: Try to use both options: separate table "video_log" for occasional statistics with ip, date viewed, user id ... and grand total in table "video" for displaying on each request for page. if I have 20,000 videos, this would do around 40,000 queries SELECT COUNT(video_id) FROM video_log GROUP BY (video_id) - this is 1 query for select. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.