Always add DB index

13

Dislike

ihower Posted by ihower on July 24, 2010

Always add index for foreign key, columns that need to be sorted, lookup fields and columns that are used in a GROUP BY. This can improve the performance for sql query. If you're not sure which column need to index , I recommend to use http://github.com/eladmeidar/rails_indexes, which provide rake tasks to find missing indexes.

Bad Smell

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

  def self.down
    drop_table "comments"
  end
end

By default, rails does not add indexes automatically for foreign key, you should add indexes by yourself.

Refactor

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

    add_index :comments, :post_id
    add_index :comments, :user_id
  end

  def self.down
    drop_table "comments"
  end
end

This is a basic practice, follow it.

implemented
migration
related best practices:

Comments

Ninad Pachpute Posted by Ninad Pachpute on 2010-07-25T15:37:29Z

Can index be a non-integer, something like a permalink?

anonymous Posted by svesely on 2010-07-25T16:05:39Z

Ninad- most of the relational databases you'll run into will let you index a string, as well.

anonymous Posted by snatcher on 2010-07-25T18:20:40Z

One may use http://github.com/mlomnicki/automatic_foreign_key gem which adds an index always when foreign_key is created. Just enable auto_index option.

mikhailov Posted by mikhailov on 2010-07-26T04:09:40Z

I'm using on Rack-based database indexes management tool. It's very suitable along with "tail -f log/development.log" monitoring and add/delete indexes on fly. http://github.com/mikhailov/DbIndexes

flyerhzm Posted by flyerhzm on 2010-07-27T01:15:41Z

When I built the rails-bestpractices.com website, I used http://github.com/eladmeidar/rails_indexes gem to add all indexes for foreign key. It's very easy.

iGEL Posted by iGEL on 2010-07-27T13:34:31Z

You should NOT use :force => true in migrations. It is added to the schema.rb automatically, so remove if you copy from your schema. That way, if that migration is executed again for some strange reason, it will drop your existing table with all data and recreate it instead of failing.

Lost a live database over this once...

ihower Posted by ihower on 2010-07-27T21:21:11Z

@iGEL: you're right. I hate :force => true too.

anonymous Posted by kayluhb on 2011-02-15T19:27:15Z

http://github.com/eladmeidar/rails_indexes fails in Rails 3.

flyerhzm Posted by flyerhzm on 2011-02-16T02:20:08Z

@kayluhb, forget the rails_indexes plugin, rails_best_practices is smart enough to help you find missing indexes.

anonymous Posted by Mailo on 2012-01-05T16:18:06Z

Hi i am getting strange behavior

schema:

  create_table "taggings", :force => true do |t|
    t.integer  "tag_id"
    t.integer  "taggable_id"
    t.string   "taggable_type"
    t.integer  "tagger_id"
    t.string   "tagger_type"
    t.string   "context"
    t.datetime "created_at"
  end

  add_index "taggings", ["tag_id"], :name => "index_taggings_on_tag_id"
  add_index "taggings", ["taggable_id", "taggable_type", "context"], :name => "index_taggings_on_taggable_id_and_taggable_type_and_context"
  add_index "taggings", ["tagger_id"], :name => "index_taggings_on_tagger_id"
  add_index "taggings", ["tagger_type"], :name => "index_taggings_on_tagger_type"


terminal/rails_best_practices:

./db/schema.rb:59 - always add db index (taggings => [tagger_id, tagger_type])


using:

ruby 1.9.3p0 (2011-10-30 revision 33570) [x86_64-darwin11.2.0]
Rails 3.0.1


Is that correct behavior for rails_best_practices?

flyerhzm Posted by flyerhzm on 2012-01-06T00:53:54Z

@Mailo, it's not a correct behavior for rails_best_practices, I will fix it soon.

anonymous Posted by Giovanni on 2012-03-08T11:54:00Z

Thanks.
Post a comment

  1. Note: if you are a registered user, you can log in without input username.

    2. You may use ONLY these HTML tags to format your comment:

    <a href="" title=""> <b> <blockquote> <pre> <code> <em> <i> <strong>

    Use <pre><code>...</code></pre> for multi-line codes.

    Do not use <p> tags. Just use newlines.

Fork me on GitHub