Most Popular Videos This Week
I have Youtube as a web page where users download and watch videos.
I would like to add "Most Watched Videos This Week" to my favorite videos on the homepage. But this list should not only contain videos uploaded in the previous week, but all videos.
I am currently recording column views, so I have no information on when the video was viewed. So now I am looking for a solution to write this data.
The first is the most obvious (and correct, as far as I know): there is a separate table into which you insert a new row every time you want to write a new view (keeping the video id and timestamp). I am concerned that I am quickly getting a huge amount of data in this table, and queries using this table will be extremely slow (we get about 3 million page views per month).
The second solution is not that flexible, but simpler in the database. I would add 7 columns to the "video" table (one for each day of the week): views_monday, views_tuesday, views_wednesday, ... And increment the value in the correct column depending on the day. And I would reset the current day column to 0 at midnight. Then I could easily get the most popular videos of the week by summing these 7 columns.
Do you think I should be worried about the first solution or the second will be sufficient for my case? If you have a better solution please share!
I am using MySQL.
a source to share
You can also create a table showing the date, video and number of views. Every time a video is viewed, you simply increase the number of views in the form of days (or create a post if the dose does not exist). This way, you will only have one video recording per day. You can easily sum up the counter for the last week / month / year by querying this table.
a source to share