Indexes in Ruby on Rails

Posted by Scott Sun, 29 Apr 2007 03:17:00 GMT

With the powerful object model that Ruby and Rails provide, it gets easy to forget there is (usually) a database behind it all. And that it is important to tune that database just like with any other application.

A large part of database tuning is index creation and except for making the ‘id’ columns a primary key, Rails won’t do this for you automatically. So it is important that you remember to create indexes for performance gain.

Indexes

How

Indexes are both the most common and most easily implemented database performance enhancer. Rails makes it easy to add indexes by way of migrations.


def self.up
  add_index :accounts, :account_name
end

def self.down
  remove_index :accounts, :account_name
end

Where

While indexes are syntatically simple to create in Rails, it still takes knowledge of effective database design to understand when and where to add indexes.

Here are the common sense candidates for indexing.

Create indexes when a column is often in the WHERE clause and the column’s values are fairly distributed. For Rails: when an attribute is often on either side of an equal sign (’=’), that attribute’s database column should probably be indexed.

Create indexes when a column is read as part of the WHERE clause much more often than it is written or updated. This is because everytime a value in an indexed column is written or updated, the database must take time to update the index as well.

The distribution of column values is an important consideration. If a column holds only two or three values (such as a boolean value), then it is often not worth creating the index. Think of it this way, if the index only narrows the dataset down to “this half”, then it may not be worth the overhead of maintaining the index.

Are there any columns that are read as part of before or after filters? A common example is authorization. Common authorization schemes have before filters on almost every controller. The before filter might authorize the user to access a record by comparing a session variable or portion of the url to a user and an account object. This means there could be (depending on caching activities) database queries against the users and accounts table at every turn. Even if your tables do not have millions of records, adding an index could cut the database time by tenths of seconds. That might not seem like much, but when you consider that an action might call authorization against several different models/tables, it can all add up.

If you do not have indexes in your Ruby on Rails app, go ahead and take some time now to add them.