MySQL storing view data over time, how to reference

Question

Tables:

videoID (vidID, vidName, vidURL) etc.

viewCount (vidID, views, timestamp)

The main goal of this database is to calculate the slope between the most recent view and the previous one.

Lets say I automatically store the view data in the database once everyday.

This means all the view data for all videos is in one big table called viewCount. The views are linked to the associated video with the vidID. Would this set up be an acceptable solution? Mainly how can I retrieve those two values needed to calculate slope for each video from this current database set up?

Answer 1

why dont you use a summary table ("materialised view") such as the following which stores viewings by year and week. You can change the level of granularity to whatever you want - monthly, daily etc...

drop table if exists video_counts;
create table video_counts
(
year_id smallint unsigned not null,
week_id tinyint unsigned not null,
video_id int unsigned not null,
counter int unsigned not null default 0,
primary key (year_id, week_id, video_id) -- note clustered index (innodb only)
)
engine=innodb;

insert into video_counts (year_id, week_id, video_id, counter) values
(2010,1,1, 90), (2010,2,1, 80), (2010,3,1, 70),
(2010,1,2, 30), (2010,2,2, 50), (2010,3,2, 30);

update video_counts set 
  counter = counter + 1 
where 
  year_id = year(now()) and week_id = week(now()) and video_id = 1;