Проблема Эффективности MySQL - Как найти правильный баланс нормализации …?

вот как я бы подошел к проблеме http://pastie.org/879604

drop table if exists picture;
create table picture
( 
 picture_id int unsigned not null auto_increment primary key,
 user_id int unsigned not null, -- owner of the picture, the user who uploaded it
 tot_votes int unsigned not null default 0, -- total number of votes 
 tot_rating int unsigned not null default 0, -- accumulative ratings 
 avg_rating decimal(5,2) not null default 0, -- tot_rating / tot_votes
 key picture_user_idx(user_id)
)engine=innodb;

insert into picture (user_id) values 
 (1),(2),(3),(4),(5),(6),(7),(1),(1),(2),(3),(6),(7),(7),(5);


drop table if exists picture_vote;
create table picture_vote
( 
 picture_id int unsigned not null,
 user_id int unsigned not null,-- voter
 rating tinyint unsigned not null default 0, -- rating 0 to 5
 primary key (picture_id, user_id)
)engine=innodb;

delimiter #

create trigger picture_vote_before_ins_trig before insert on picture_vote
for each row
begin
 declare total_rating int unsigned default 0;
 declare total_votes int unsigned default 0;

 select tot_rating + new.rating, tot_votes + 1 into total_rating, total_votes 
   from picture where picture_id = new.picture_id;

 -- counts/stats
 update picture set
    tot_votes = total_votes, tot_rating = total_rating, 
    avg_rating = total_rating / total_votes
 where picture_id = new.picture_id;

 end#
 delimiter ;

надеюсь, что это поможет :)

Хороший способ насладиться обоими мирами - с помощью Mysql Trigger. http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Теперь добавьте триггер, который когда-либо оценивает изображение, он будет обновите avg_rating в таблицах изображений. (используя тот же выбор, который вы указали)

Теперь, когда вы выбираете, вы можете выбирать только на одной таблице. И он всегда обновляется. А если вы хотите получить точную информацию о том, кто оценивает какую картинку, вы также можете выбрать ее из рейтинговой таблицы.

Что будут содержать эти поля ratingOne к ratingFive? Количество полученных голосов? Тогда вы не узнаете, кто голосовал. Если вам действительно нужно денормализовать, я бы просто добавил поле «средний рейтинг» в графическую таблицу и обновлял его при каждом голосовании (и оставил таблицу оценок как есть).

В общем, не попадитесь преждевременной оптимизации. Попробуйте написать тестовый сценарий, который создает 100 000 изображений и 1 миллион оценок (или любую другую цифру, которую вы хотите поддержать), и посмотрите, сколько времени займет ваш запрос AVG. Скорее всего, все еще будет достаточно быстро. Убедитесь, что ваша таблица "рейтингов" имеет индекс по идентификатору изображения, чтобы БД не нужно было просматривать миллион строк.

В мире РСУБД денормализация означает «Я хочу повысить эффективность запросов за счет увеличения объема обслуживания , сохраняя при этом правильность модели »

В вашем случае эффективность действительно немного повысится (поскольку все рейтинги всегда берутся с одной и той же страницы данных).

А как насчет правильности модели?

При таком дизайне вы, во-первых, не знаете, кто голосовал (эта информация больше не сохраняется), а, во-вторых, не можете оценить картинку более пяти раз.

Поскольку в вашей исходной модели не было ни одного из этих ограничений, я считаю, что такая денормализация - не то, что вам действительно нужно.