Better Performance on Associations

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!

From stackoverflow

• 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.

  Andrew Vit : I believe it does the same thing in this case. Two queries would be more efficient here anyway, since you wouldn't want it to LEFT JOIN all the hits onto the campaign row. All you need essentially is "WHERE campaign_id = 30"

• 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.

  Garrett : Undefined error, it shouldn't know to find the 30 ID anyways.

  Garrett : Oh! This looks awesome, thank you!