Hack to Learn

Between the time your migration files get generated and deploying to production with your new application you will likely need to add indexes to one or more of your tables. How do you know which tables need indexes and which fields in those tables to index?

Here are some tips to help figure out where the likely bottlenecks will occur with regard to your database tables:

1. Always index @*_id@ foreign_key fields. Let’s say you have 2 tables, @users@ and @addresses@. You may have migration file code in @./db/migrate@ that looks like this:

  ...
  def self.up
    create_table "users", :force => true do |t|
      t.column :login, :email             :string
    end
  end
  ...

  ...
  def self.up
    create_table "addresses", :force => true do |t|
      t.column :user_id       :integer
      t.column :address       :string
    end
  end
  ...

You will always want to index the foreign_key @user_id@, with few exceptions. So simply add the @add_index@ line like this:

  ...
  def self.up
    create_table "addresses", :force => true do |t|
      t.column :user_id       :integer
      t.column :address       :string
    end
    add_index "addresses", :user_id
  end
  ...

This will facilitate much faster associations when you lookup related models like this view code:

  <%=h user.address.address %>
  
  # OR as the case may be...
  
  <%=h address.user.email %>

1. As a rule of thumb index any column for which you will be using to lookup a row or set of rows. For example, if you will be doing something like this:

  u = User.find_by_email('test@example.com')

… then you will want to index the email field.

1. Run explain plans on your associations to determine where indexes would benefit the queries. Now this one is more tricky unless you know about the query_analyzer plugin. This nifty little tool will automatically dump out explain plans into your logfiles in development mode that look like this example from the README file:

# development.log

P Load (0.008669)
 => SELECT p.* FROM p INNER JOIN d ON p.id = d.p_id WHERE (d.p_id = 2 AND ((d.type = 'P')))

Analyzing P Load

select_type | key_len | type   | Extra       | id | possible_keys | rows | table  | ref    | key
 ----------------------------------------------------------------------------------------------------
SIMPLE      |         | ALL    | Using where | 1  |               | 74   |    d   |        |
SIMPLE      | 4       | eq_ref | Using where | 1  | PRIMARY       | 1    |    p   | d.p_id | PRIMARY

Now what this tells you is that the table @d@ is missing an index because MySQL is having to look at “ALL” the records. Whenever you see a @type@ of @ALL@ and/or @possible_keys@ is empty it should be a red flag telling you to index something.

If we had followed tips 1 and 2 this explain plan would be much different, showing reference types of @ref@ and @eq_ref@ which is typically a good thing.

For more information see the README file here: http://agilewebdevelopment.com/plugins/query_analyzer.