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,
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.
精彩评论