Better Performance on Associations

Question

Right now I have a table called Campaigns that has many Hits, if I call say:

Campaign.find(30).hits

Which takes 4 seconds, or 4213 ms.

If I call this instead:

campaign = Campaign.find(30)
campaign.hits.count

Does it still load all of the hits, then count? Or does it see I am counting and avoids loading all of the hits? (Which is currently 300,000+ rows).

I am trying to figure out a smart way to load/count my hits. I am thinking about adding a method to my Campaign.rb model, like:

def self.total_hits
   find :first, :select => 'COUNT(id) as hits', :conditions => ["campaign_id = ?", self.id]
end

I know that query won't load from the hits table, but that is just an example of counting it from a self made query, apposed to Ruby on Rails doing this for me.

Would this memcache query be more effecient? (I have it running, but doesn't seem to be any better/faster/slower, just the same speed.)

def self.hits
    Rails.cache.fetch("Campaign_Hits_#{self.campaign_id}", :expires_in => 40) {
      find(:first, :select => 'COUNT(id) as hits', :conditions => ["campaign_id = ?", self.campaign_id]).hits
    }
end

Any suggestions would be great!

Answer 1

My ActiveRecord might be a little rusty, so forgive me if so, but IIRC Campaign.find(30).hits is at least two separate queries. How does Campaign.find(30, :include => [ :hits ]).hits do? That should perform a single query.

Answer 2

How about:

Campaign.find(30).hits.count

You might also consider adding the following in hit.rb (assuming a one-to-many relationship between campaigns and hits).

belongs_to :campaign, :counter_cache => true

You then need a column in the campaigns table called hits_count. This will avoid hitting hits altogether if you're only getting the count.

You can check the API for the full rundown.