Adding index in Rails has-many-through relationship

Question

With the below relationship in mind:

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 would I most efficiently add indexes to speed up this method in the Style model:

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

Tilo · Accepted Answer · 2018-06-06 23:32:18Z

8

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 on the :features class unique, but beware of this catch:

If you have records which can contain NULL / nil fields which are part of the index, then don't use unique indexes. => check your data first

If during deletion of features it could happen that a StyleFeatures entry gets a nil reference (instead of being deleted altogether), then having a unique index will also cause problems for that table.

Make sure to double-check on how your particular database handles indexes when querying on null values.

See: Rails uniqueness constraint and matching db unique index for null column

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

edited Jun 6, 2018 at 23:32

answered Sep 20, 2013 at 22:19

Tilo

33.8k5 gold badges83 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tilo Over a year ago

see comment in my answer

Abram Over a year ago

As it turns out I already have this in my schema add_index "stylefeatures", ["feature_id"], :name => "index_stylefeatures_on_feature_id" add_index "stylefeatures", ["style_id"], :name => "index_stylefeatures_on_style_id"

Abram Over a year ago

I will drop the index and re-add it.

Almaron · Accepted Answer · 2013-09-20 22:09:59Z

2

I'd recomend adding a unique index on stylefeatures style_id and feature_id (as an array) and a unique index on features.name.

answered Sep 20, 2013 at 22:09

Almaron

4,1876 gold badges29 silver badges49 bronze badges

1 Comment

Abram Over a year ago

AS IN add_index :stylefeatures, [:style_id, :feature_id], :unique => true AND add_index :features, :name, :unique => true

Martin Fenner · Accepted Answer · 2014-10-05 17:55:27Z

1

Small change to answer by Tilo: 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

answered Oct 5, 2014 at 17:55

Martin Fenner

762 silver badges2 bronze badges

Collectives™ on Stack Overflow

Adding index in Rails has-many-through relationship

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related