Konstantin Saveljev wrote:
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 ?
It depends really. If it's something they are going to do all the time,
they'd probably use a trigger or work it out in the application and do
the update "manually" (eg if they are using mysql 4 which doesn't
support triggers).
If it's something that they run once a month for their reporting, they'd
probably run it "as is".
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
