Rails and How to Index Your DB Tables
Posted by Chris Blackburn
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 | PRIMARYNow 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.