Optimize ActiveRecord Query

Question

I was working on optimizing my Rails application, but I was stuck in one specific request:

def self.random_selection(n) items = scoped(:joins => "JOIN (SELECT id FROM #{table_name} WHERE medias_count > 0 ORDER BY RAND() LIMIT #{n.to_i} ) AS random_ids ON #{table_name}.id = random_ids.id" ) items.each do |genre| genre.medias.sort! do |x,y| y.vote_total <=> x.vote_total end end items end 

The idea is that she will choose a series of random genres in which there is media. After choosing it, it will be sorted by the highest nominal media, I think I will take this "upper media" and use it in the view.

This is a rather expensive, ugly request, and I would like some approaches to optimize it.

Can I flip the media selection to the original request?

Should I approach this from a different direction and choose random high-rated medians and extract a genre from them? (also acceptable, but if he does not offer any improvement, then they do not make sense)

I use Rails 3, Ruby 1.9.2 and MySQL with InnoDB.

Answer 1

My decision

 class Genre scope :having_media, where('medias_count > 0') scope :random, lambda { |limit| where(:id => random_ids(limit)) } def self.random_ids(limit) having_media.select('id').sample(limit).map(&:id) end def self.random_selection(limit) random(10).includes(:medias).each do |genre| genre.medias.sort! do |x,y| y.vote_total <=> x.vote_total end end end end class Media scope :voted_higher, reorder('vote_total desc') end @random_genres = Genre.random_selection(10)