Optimise ActiveRecord Query

Question

I've been working through optimising my Rails application but I'm stuck one particular query:

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 it will select a number of random genres that have media within them. Once selected, it will then sort on the highest rated media, I think take that "top media" and use it within the view.

This is quite an expensive, ugly query and I'd like some approaches I could take on optimising it.

Could I roll the selection of medias into the original query?

Should I approach this from the other direction and select random high rated medias and fetch the genre from them? (also acceptable, but if it doesn't offer any improvement then theirs no point)

I'm using Rails 3, Ruby 1.9.2 and MySQL with InnoDB.

It would be helpful if you can post a slimmed down example of your tables related to this query. Also, which model is the above method in? — rjk, May 26 '11 at 02:21
See answers to http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql — Steve Jorgensen, May 26 '11 at 02:55
Two tables. Genre and Media Medias belong to Genre through a many to one. — Samuel, May 26 '11 at 03:30

score 1 · Accepted Answer · answered May 26 '11 at 08:34

My solution

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)

Optimise ActiveRecord Query

1 Answers1