Rating System Query

Question

I've built a rating system for movies in MySQL, however the concern is that when my query sums up all the ratings and divides it, it might have to sum up millions and millions of records.

One solution I thought of was to basically cache the rating in memcached and only do the rating operation for items which are not used a lot. However even then for movies which are not being rated a lot if someone does go check the rating that query might take a long time if it has to calculate a lot of rows.

Another solution i thought of was to build a temporary table which is constantly updating the table, however what if the movie is being rated a lot and someone is trying to access it, INNODB i believe does row locking, so will this cause a deadlock or something in the long run?

Answer 1

Since ratings are fairly static and your dataset is large, you could cache (denormalize) the data in the movie record when a user rates a movie. You need to keep track of the number of both votes and the sum of the votes, so the average is accurate. Your movie rating is dynamically calculated when you need it. Here's the schema:

create table movie as (
movie_id int not null primary key,
-- your current columns
vote_count int,
vote_sum int
);

then use a view to help out

create view movie_view as
select
  *,
  vote_sum/vote_count as vote_average
from movie;

assuming you have a table that looks like this:

create table user_movie_vote (
user_id int references user,
movie_id int references movie,
vote int
);

you could use a trigger to keep the vote totals up to date for you:

delimiter ~
create trigger movie_vote_trg after insert on user_movie_vote
for each row
begin
  update movie set
  vote_count = vote_count + 1,
  vote_sum = vote_sum + new.vote
  where movie_id = new.movie_id;
end~
delimiter ;

if votes can be updated, you'll need:

delimiter ~
create trigger movie_vote_trg after update on user_movie_vote
for each row
begin
  update movie set
  vote_sum = vote_sum + new.vote - old.vote
  where movie_id = new.movie_id;
end~
delimiter ;

if votes can be deleted, you'll need:

delimiter ~
create trigger movie_vote_trg after delete on user_movie_vote
for each row
begin
  update movie set
  vote_sum = vote_sum - old.vote,
  vote_count = vote_count - 1
  where movie_id = new.movie_id;
end~
delimiter ;