need solution for ORDER BY and COUNT

[Konstantin S.]

Hi,

let's say i have a system with lot's of users ( 200k ) and lot's of media files ( 500k )

users can set a media file as their favorite media.
CREATE TABLE file_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 there are 1M entries in that table



So the question is: what is the best way to get Most Favorite media?

For that we need something like
SELECT media.*, COUNT(file_fans.id) AS 'count'
FROM media, file_fans
ORDER BY count DESC
LIMIT 100

^how to set up indexes in that sort of query in order not to make filesort and other stuff that makes query slower...

or: is it better to have some other table that will have a total count of fans for each media file?

so there will be some table and it'll use some trigger in order to increment the amount of fans for a media:
CREATE TABLE fans_count(
id int(11) unsigned not null auto_increment,
media_id int(11) unsigned not null,
fans_count int(11) unsigned not null, # this column will be indexed
primary key(id),
foreign key(media_id) references media(id) on delete cascade
)Engine=InnoDB;

and then it is possible to just use that table in order to get some optimized query and fetch needed results ( and not to count the number of fans every time )




So what i really wanna know: is it possible to optimize the first variant query ( and how to optimize it if it's possible ) and is it a good approach to have some table with precalculated results



Edited 1 time(s). Last edit at 01/25/2007 05:47AM by Konstantin S..