3

Let's say I have a rails app with 3 tables, one for questions, one for options (possible answers to this question), and one for votes.

Currently, when requesting the statistics on a given question, I have to make a SQL query for each option which will look in the "votes" table (around 1.5 million entries) and count the number of times this option has been selected. It's slow and takes 4/5 seconds.

I was thinking of adding a column directly in the question table which would store the statistics and update them each time someone makes a vote. Is that good practice ? Because it seems redundant to the information that is already in the votes table, only it would be faster to load. Or maybe I should create another table which would save these statistics for each question ? Thanks for your advice !

Emmanuel
  • 254
  • 3
  • 11

3 Answers3

3

Rails offers a feature called counter_cache which will serve your purpose

Add the counter_cache option to votes model

   class Vote < AR::Base
       belongs_to :question, :counter_cache => true
   end

and the following migration

add_column :questions, :votes_count, :integer, :default => 0

This should increment the votes_count field in questions table for every new record in votes table

For more info: RailsCast

dexter
  • 13,365
  • 5
  • 39
  • 56
  • 1
    I think `Vote belongs_to :option` not to `:question`, but yeah this is basically what he should use. – Mischa Sep 20 '11 at 09:42
2

It would be a wise decision, ActiveRecord:CounterCache is made just for that purpose.

Also, there's a Railscast for that

edgerunner
  • 14,873
  • 2
  • 57
  • 69
  • 1
    Also check out this [screencast](http://railscasts.com/episodes/23-counter-cache-column). – Mischa Sep 20 '11 at 09:38
0

You probably can do a "clever" SQL query using GROUP BY that will give you the expected result in one query. If you are query is that slow you'll probably need to add some indexes on your table.

mb14
  • 22,276
  • 7
  • 60
  • 102
  • Yes I thought about doing that too but even with a "clever" group by it's still a bit slow, I'll try the counter_cache solution above – Emmanuel Sep 20 '11 at 10:02