When you create your tables through Rails migration, did you ever think about adding a index? I bet that most of you just ignore it, especially when startup and everything might change a lot later. I ignored index too, but when our projects come to production mode, I start to realized that we really really need to optimize our database.

It’s super easy to add and remove a index through migration:

def self.up
  add_index :students, :name
end

def self.down
  remove_index :students, :name
end

But the difficulty is that you need to find the right fields to index and after that, you need a way to check if it helps. The difficulty increases as your projects grows, i.e. when there are many of fields and relationships between tables. You wouldn’t know what’s the real neck lock through a glance. Fortunately, there are tools that could be used to analyze your existing system and locate what could be improved.

I use jeberly’s query-analyzer to help. After install query-analyzer, you could tail log/development.log, and then start to use your website. “Each SQL select query will be ‘EXPLAIN’ed and added to the log files right below the original query.”

Student Load (0.3ms)   SELECT * FROM `students` WHERE (name = 'mike') LIMIT 1
  ############ FIXME - UNOPTIMIZED QUERY for Student Load ############ 
   select_type | key_len | type | Extra       | id |
   ---------------------------------------------------------... ...
   SIMPLE      |         | ALL  | Using where | 1  |

I suggest you go through the Example Use in query-analyzer. It covers usage, results analysis and adding/removing indexes. Here, what I would like to add is how to speed up adding index and reviewing the improvement. It’s pain that you have to write one migration file, then migrate it before you could see the improvement, especially when you have tens of FIXME warnings in the log. My tricks is to do experiment part through script/console, and after you get enough information, you could simply add a migration file with a batch of add_index. That would save a lot of time. Below is how I did in one of our projects:

ruby script/console
ActiveRecord::Base.connection.add_index :students, :name
# You could do this through the model class too
Student.connection.add_index :students, :name

From the log, you could see something like this: CREATE INDEX `index_students_on_name` ON `students` (`name`)

And if you want to remove the index:

ruby script/console
ActiveRecord::Base.connection.remove_index :students, :name
# You could do this through the model class too
Student.connection.remove_index :students, :name

From the log, you could see something like this: DROP INDEX `index_students_on_name` ON `students`

After adding the index, you could refresh your web page, and check the log, see if there are still warnings from query-analyzer.

Note that there are 4 types of index:

1. “Normal” Indexes
2. Unique Indexes
3. Primary keys
4. Full-text indexes

The one we talked above is the “Normal” Indexes. I suggest you check this article out for more about indexes.