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.