Ruby on Rails Database Indexes
Contact me at LinkedIn  RailsZilla at Facebook  RailsZilla at twitter   google +1  Contact me at Xing  connect me at github

Ruby on Rails Database Indexes

Posted in Rails

Handy hint for better database performance in Ruby on Rails:

Always add DB index in your Migration!
By default, rails does not add indexes automatically for foreign key, you should add indexes by yourself.
If you want to speed up your performance, I suggest to add an 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!

Let’s go on with our sample Migration for Databases. Go to your console / terminal and enter:

1
rails generate migration adding_columns_to_database

open the migration file in your editor / IDE and enter:

1
2
3
4
5
6
7
8
9
10
11
12
class CreateImages < ActiveRecord::Migration
  def self.up
    create_table "images" do |t|
      t.string :path
      t.integer :user_id
    end
  end

  def self.down
    drop_table "images"
  end
end

This would be the code if we would have used scaffold generator.
Rails does not add indexes automatically for foreign key, as you see.

This has to be added to our migration:

1
add_index :images, :user_id

Let’s have a look at our complete database migration:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
class CreateImages < ActiveRecord::Migration
  def self.up
    create_table "images" do |t|
     t.string :path
     t.integer :user_id
    end

    add_index :images, :user_id
  end

  def self.down
    drop_table "images"
  end
end

However, there are a few cases which should not use indexes even in GROUP BY or lookup columns.
If you are using B-Tree indexes, you should not use them if the data has few distinct values (for example a male/female field). These can actually slow the query down.

For fields that are updated often, the overhead for recalculating the index with each addition/ update might not be worth the speed gained from having the index.

Tags: , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter a secure code to see if you are a spammer ;-)

 

Copyright © 2011-2017  - RailsZilla – Ruby on Rails tutorials, tips and tricks All rights reserved. | Imprint | Privacy