Suppose a table videos
id | name | views
----------+--------------+-----------
1 | Video1 | 52
2 | Video2 | 150
...
For getting the video which is popular/most viewed this week, I could create another table: videoviews
id | foreign_key | viewed_on
----------+--------------+-----------
1 | 1 | 10/12/2018
2 | 1 | 09/12/2018
...
From this table, I can easily get the data for last week/last month etc. That's not an issue.
Problem: Suppose I have 1000 Videos and Each video gets 100 Views per day. My videoviews table will have 100000 records each day.
I know this is not the best way to achieve this functionality. Just wondering what is?
I found these on SO but..
Problem: Suppose I have 1000 Videos and Each video gets 100 Views per day. My videoviews table will have 100000 records each day.
Do you need a complete record of each individual view?
You could, instead, use a counter approach, where you store one row per video per day, and simply increment its value when a new row comes in. This is granular enough to provide useful per-day analytics, without having to store a million rows for a million video views.
Add extra columns called views and start_date on your videoviews table.
On hitting the page with the video, fetch the views, increment, and update where the week starts with start_date.
Only one row is required per week. You can also remove old weeks if you like.