Adding an index to Rails has a multi-pass relationship

Question

Given the following considerations:

class Style < ActiveRecord::Base has_many :stylefeatures, :dependent => :destroy has_many :features, :through => :stylefeatures end class Stylefeature < ActiveRecord::Base belongs_to :style belongs_to :feature end class Feature < ActiveRecord::Base has_many :stylefeatures, :dependent => :destroy has_many :styles, :through => :stylefeatures end 

How can I most effectively add indexes to speed up this method in a style model:

  def has_feature? (arg) self.features.where(:name=>arg).exists? end 

Answer 1

 class AddIndexesToStyleFeatures < ActiveRecord::Migration def self.up add_index :stylefeatures , [:style_id , :feature_id] , :unique => true add_index :features , :name # check your data before making this unique end def self.down drop_index :features , :name drop_index :stylefeatures, [:style_id , :feature_id] end end 

You might want to make the: name index of the: features class unique, but beware of this catch:

If you have entries that may contain NULL / nil fields that are part of an index, then do not use unique indexes. => check your details first

If during the removal of functions it may happen that the StyleFeatures element gets a reference to nil (instead of being deleted at all), then the presence of a unique index will also cause problems for this table.

See: Restricting Rails Uniqueness and Matching a Unique Db Index for a Zero Column

and: How to create a unique index in a NULL column?

Answer 2

I would recommend adding a unique index to stylefeatures style_id and feature_id (as an array) and a unique index on features.name .

Answer 3

Tilo answer small change: use remove_index instead of drop_index :

 class AddIndexesToStyleFeatures < ActiveRecord::Migration def self.up add_index :stylefeatures , [:style_id , :feature_id] , :unique => true end def self.down remove_index :stylefeatures, [:style_id , :feature_id] end end