Rails and How to Index Your DB Tables

Posted by Chris Blackburn Thu, 06 Nov 2008 22:48:00 GMT

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 %>

2. 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.

3. 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.

Posted in  | Tags , , , , , ,  | 2 comments | 1 trackback

Mysql::Error: Lost connection to MySQL

Posted by admin Thu, 18 Jan 2007 14:32:00 GMT

If you are getting this error message when trying to use RoR ActiveRecord, there is a simple workaround… read on.

MySQL has a problem with how Rails sends the username and password when initiating the connection through the socket.

A quick fix/workaround is to create a MySQL user without a password. Add it to your database.yml file and restart your Mongrel/Webrick server.

Tags , , ,  | no comments | no trackbacks