Most watched videos this week

Question

I have a Youtube like web-page where users upload&watch videos.

I would like to add a "most watched videos this week" list of videos to my page. But this list should not contain just the videos that ware uploaded in the previous week, but all videos.

I'm currently recording views in a column, so I have no information on when a video was watched. So now I'm searching for a solution to how to record this data.

The first is the most obvious (and the correct one, as far as I know): have a separate table in which you insert a new line every time you want to record a new view (storing the ID of the video and the timestamp). I'm worried that I would quickly get huge amounts of data in this table, and queries using this table would be extremely slow (we get about 3 million views a month).

The second solution isn't as flexible but is more easy on the database. I would add 7 columns to the "videos" table (one for each day of the week): views_monday, views_tuesday , views_wednesday, ... And increment the value in the correct column based on the day it is. And I would reset the current day's column to 0 at midnight. I could then easily get the most watched videos of the week by summing this 7 columns.

What do you think, should I bother with the first solution or will the second one suffice for my case? If you have a better solution please share!

I'm using MySQL.

Answer 1

You could also create a table with the date, video id and view count. Each time a video is viewed you just increment that days view count (or create the record if is dose not exist). So that way you just have one record per day per video. You can easily sum the count for the last week / month / year by querying this table.