Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding index in Rails has-many-through relationship

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
like image 693
Abram Avatar asked Sep 20 '13 22:09

Abram


People also ask

What does add index do in Rails?

An index is used to speed up the performance of queries on a database. Rails allows us to create index on a database column by means of a migration. By default, the sort order for the index is ascending. But consider the case where we are fetching reports from the database.

What is the difference between Has_one and Belongs_to?

They essentially do the same thing, the only difference is what side of the relationship you are on. If a User has a Profile , then in the User class you'd have has_one :profile and in the Profile class you'd have belongs_to :user . To determine who "has" the other object, look at where the foreign key is.


2 Answers

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?

like image 68
Tilo Avatar answered Oct 05 '22 18:10

Tilo


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

like image 30
Almaron Avatar answered Oct 05 '22 20:10

Almaron