Improving performance of Rails model

I have the following model that allows Users to cast Votes on Photos.

class Vote < ActiveRecord::Base
    attr_accessible :value

    belongs_to :photo
    belongs_to :user

    validates_associated :photo, :user
    validates_uniqueness_of :user_id, :scope => :photo_id
    validates_uniqueness_of :photo_id, :scope => :user_id
    validates_inclusion_of :value, :in => [-2,-1,1,2], :allow_nil => true

    after_save :write_photo_data

    def self.score
        dd = where( :value => -2 ).count
        d = where( :value => -1 ).count
        u = where( :value => 1 ).count
        uu = where( :value => 2 ).count
        self.compute_score(dd,d,u,uu)
    end

    def self.compute_score(dd, d, u, uu)
        tot = [dd,d,u,uu].sum.to_f
        score = [-5*dd, -2*d, 2*u, 5*uu].sum / [tot,4].sum*20.0
        score.round(2)
    end

    private
        def write_photo_data
            self.photo.score = self.photo.votes.score
            self.photo.save!
        end

end

This functions very well, however computing the score for a photo is pretty slow - it seems to take 7-12 seconds on average. I've tried adding indices for photo_id, user_id, and o开发者_运维技巧ne combined for photo_id and value, but this hasn't really improved the performance as far as I can tell.

I'd be interested in feedback from any serious rails gurus (I'm totally an amateur) as to how this could be optimized / improved. How would you tally up votes for a particular photo and value?

Thanks!

--EDIT--

Note that the scores: -2,-1,1,2 represent "two-thumbs down, one-thumb down, thumb up, two-thumbs up", not specific values. I could match these to the values I've assigned to them in the compute score method, but I haven't done that so far because I may want to tweak the weightings over time after seeing more data accumulated.

Also, regardless of how I represent those four possible votes in the DB, I still need both the COUNT of each kind of vote as well as the weighted value of those votes for each photo to compute the score. Thanks!

---

You need an index on value, by itself. combined indexes only work when the query has both components, starting at the left. Since your where clause does not specify a photo id, it's not using your combined index.

update see http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

---

One thing you could do is asking the database once instead of four times for the score counts:

Vote.where(photo_id: photo.id).group(:value).count

would result in a single database query and give you a hash like

{-2 => 21, -1 => 48, 1 => 103, 2 => 84}

Besides that, if you store the actual values of [-5, -2, 2, 5] instead of [-2, -1, 1, 2] in the database, you could just do

Vote.where(photo_id: photo.id).sum

and get your sum direct from the database (or even use avg to get the average instead)

---

Why do you store -2, 2, 1, 2 instead of the actual grade? If you store the grade (-5 for example), you will be able to compute the score in DB directly without having to run 4 count queries. This will be an improvement for sure.

---

Putting an index on the value column will speed up the SELECTs if you have lots of records in the DB.

The above posts also bring up some good points on direct optimization. However, as your DB scales, all of these approaches will eventually fall down. Since the score is a derived value, you could cache it in Memcached, Redis, or even SQL which will ensure that fetching the score scales in constant time as the app grows. You can allow the caches to get out of date and keep them updated using a background process. By doing so, your calculation function can take arbitrarily long without impacting the user experience.