Don’t forget Indexes when using ActiveRecord

When you start a new Rails project, performance isn’t an issue. You add migrations nonchalantly as you complete more features. As your app grows and you get more users, database performance starts to become an issue.

There are many ways to diagnose and improve the database performance of a Rails application. However, this post will only focus on indexes.

Indexes

A SQL database index is a special lookup table that enables data to be retrieved more quickly. SQL indexes are like the indexes at the back of a book. If you want to find a certain page in a book, it is quicker to find it using the index than by looking through each page of the book.

An index speeds up SELECT and WHERE queries but it slows down data input (UPDATE and INSERT) statements.

Adding an index with ActiveRecord

ActiveRecord allows you to add indexes in migrations with add_index. For example, this scaffold command creates a migration to add a new column with an index:

$ bin/rails generate migration AddPartNumberToProducts part_number:string:index

The migration looks like this:

class AddPartNumberToProducts < ActiveRecord::Migration[5.0]
  def change
    add_column :products, :part_number, :string
    add_index :products, :part_number
  end
end

Adding an index for multiple columns

As well as single indexes you can also add multi-column indexes. For example, if you want to search for employees by first and last name. When creating a multi-column index you need to think carefully about which column to list first.

To understand this, imagine that you are looking up someones name in a phonebook, that is listed alphabetically by last name. If you know their last and first name, then you can use the index. If you only know their last name then you can use the index. However, if you only know their first name then you cannot use the index and you will have to go through each page of the phone book.

ActiveRecord allows you to add multi-column indexes with add_index:

add_index :last_name_first_name, [:last_name, :first_name]

Where you should add indexes

Whenever you are adding to the database structure or creating new database queries, you should think about indexes.

• If you are adding a new table that has the potential to contain a large number of records (e.g. more than 100,000) then you should think about adding an index
• If you are creating a query that joins across multiple tables then you should think about adding an index. Joining across multiple tables increases the number of records in the results set so it can be an area for performance problems
• Review you application monitoring tools regularly (e.g. NewRelic) and analyse the slowest queries. Can these queries be improved by adding an index? You can use DESCRIBE to show you which indexes the query is using