Should I add an index on a counter-cache column in Ruby on Rails 3.0

Question

Environment: Rails 3.0.4, MySQL, Ruby 1.8.7

I have the following table:

  create_table "countries", :force => true do |t|
    t.string  "iso",            :limit => 2,                 :null => false
    t.string  "name",           :limit => 80,                :null => false
    t.string  "printable_name", :limit => 80,                :null => false
    t.string  "iso3",           :limit => 3
    t.integer "numcode",        :limit => 2
    t.integer "users_count",    :default => 0
    t.integer "rank"
  end

I regularly search for countries with more than n users. Does it make sense to add an index to the counter cache 'users_count'?

I realize that it will add a slight overhead for every user added but I want to be sure that there is not something else that I'm missing in the mechanics of counter caches,

Answer 1

As you say, adding an index will add a slight overhead for every database write to the countries table, in exchange for speeding up queries that use the column.

As a rule of thumb, unless you have a very high volume of writes it's probably worth adding the index if you do any significant number of queries that reference the users_count column in the where clause of the query.

Rails counter cache functionality simply increments the users_count value when a user that belongs_to the country is created, and decrements it when the user is destroyed.