Hi, i have a system that has media files ==> users can be fans of media ( so media becomes their favorite ).
the table itself: CREATE TABLE media_fans( id int(11) unsigned not null auto_increment, user_id int(11) unsigned not null, media_id int(11) unsigned not null, primary key(id), foreign key(user_id) references users(id) on delete cascade, foreign key(media_id) references media(id) on delete cascade )Engine=InnoDB; let's say we have 1M entries in this table... How to get the Top Favorite media very quickly ? ( let's say You Tube has the sorting possibility and they have a huge amount of users and files and fans of files ) I can see 2 solutions: 1) using this sort of query: SELECT COUNT(id) as 'count' FROM media_fans ORDER BY 'count' DESC LIMIT 100 so if we have 1M rows then the 'EXPLAIN' says that we need to go through all 1M rows and type is 'INDEX' 2) using another table to store the total amount of fans for each media this solution requires another table and some triggers, so when the fan is added to media_fans some trigger works and increments a value for some media ( and then we just select the top favorite media without counting the number of fans every time ) So what i'm interested in: how do huge portals implement that ? do they recount each time ( isn't it slow ? ) or they use precalculated values ?
