creating indexes for all database tables on fly in rails

Hi,

Indexing plays a major role in database query optimisation. So it is suggested to have index for foreign_key columns.

Coming to rails migrations, our migration will not create indexes for the columns with foreign keys. Where you need to create manually those indexes.


Normal migration will be as below

class CreateArticle < ActiveRecord::Migration
  def self.up
    create_table "articles" do |t|
      t.string :content
      t.integer :post_id
      t.integer :user_id
    end
  end

  def self.down
    drop_table "articles"
  end
end


Inorder to add indexes, add the indexes as below:

class CreateArticle < ActiveRecord::Migration
  def self.up
    create_table "articles" do |t|
     t.string :content
     t.integer :post_id
     t.integer :user_id
    end

    add_index :articles, :post_id
    add_index :articles, :user_id
  end

  def self.down
    drop_table "articles"
  end
end


In some cases we may not sure which column need to index, In that case I strongly recommend to use the below gem which creates indexes for all the foreign_key columns on fly by running a simple command.

gem rails_indexes(A rake task to track down missing database indexes.) https://github.com/umamahesh/rails_indexes

usage:

add the gem to your gem file.

gem "rails_indexes"

> bundle install

> rake db:index_migration

Display a migration for adding/removing all necessary indexes based on associations:


Thank You,
Uma Mahesh.