Adding missing indexes in your Rails App

Indexing your database is an important and often overlooked performance boost to your app.

Rails makes it pretty easy to index model relations with the add_reference method in your migrations. The add_reference method will not only create a _id column on your parent table, but also add an index for _id.

If you need to add an index to an existing relation you can use the add_index method.

If you weren't in the habit of using add_reference or manually indexing your relations from the beginning it can be a pain to find all of your missing indexes. Luckily in my infinite quest to find the easiest or quickest way to do things (aka I'm super lazy) I stumbled upon a script that will find any missing indexes.

From inside you rails console run the following:

c = ActiveRecord::Base.connection

c.tables.collect do |t|  
  columns = c.columns(t).collect(&:name).select {|x| x.ends_with?("_id" || x.ends_with("_type"))}
  indexed_columns = c.indexes(t).collect(&:columns).flatten.uniq
  unindexed = columns - indexed_columns
  unless unindexed.empty?
    puts "#{t}: #{unindexed.join(", ")}"
  end
end

credit: http://tomafro.net/2009/09/quickly-list-missing-foreign-key-indexes (which is currently not working, hence this post)

What will be returned is a list of tables and the columns within them that are missing an index.