Inspect migrations to get an idea of how we define and use indexes in a Rails project.

index_stats.rb

apps = [
  "/path/to/rails/project/root",
  "/path/to/another/rails/project/root"
]

def number_of_migrations_since_first_reference(attribute, directory, indexed_in)
  grep_command = "grep -H \"#{attribute.gsub(/[\[\]\s]/,'')}\" #{directory}/*.rb"
  all_refs = `#{grep_command}`
  return -1 unless all_refs.length > 0 # probably defined with t.references :other_table

  first_ref = all_refs.split("\n")[0].split(":")[0].split("/")[-1]
  indexed_in = indexed_in.gsub(directory, "")
  all_migrations = `ls -l #{directory}`.split("\n")
  start = -1
  finish = -1
  all_migrations.each_with_index do |migration, i|
    start = i if migration.index(first_ref) && start == -1
    finish = i if migration.index(indexed_in) && finish == -1
  end
  raise "Could not find the migration of first reference" if start == -1 
  raise "Could not find the indexing migration" if finish == -1
  finish - start
end

apps.each do |app|
  directory = "#{app}/db/migrate"
  indexes = `grep -H add_index #{directory}/*.rb`
  # expected output of above is a variable number of lines 
  # matching this structure:
  # path/to/file.rb: [\s]* add_index :table, attribute(s), options
  num_indexes = 0
  num_multi_attribute_indexes = 0
  num_single_attribute_indexes = 0
  num_single_attribute_indexes_that_look_like_foreign_keys = 0
  count_of_migrations_between_index_and_attribute_first_appearance = Hash.new{|h,k| h[k] = 0 }
  indexes.each_line do |line|
    data = line.split(/:/)
    file_path = data[0]
    
    indexed_attributes_and_options = data[1..-1].join(":").split(/,/)[1..-1].join(",")
    start_of_options_hash = /([^\s\[]:)|(=>)/
    has_options = indexed_attributes_and_options =~ start_of_options_hash
    if has_options
      indexed_attributes = indexed_attributes_and_options.split(start_of_options_hash)[0].split(/,/)[0..-2]
    else
      indexed_attributes = indexed_attributes_and_options.split(/,/)
    end
    
    num_indexes += 1
    if indexed_attributes.count > 1
      num_multi_attribute_indexes += 1
      indexed_attributes.each do |attribute|
        distance = number_of_migrations_since_first_reference(attribute, directory, file_path.split("/")[-1])
        count_of_migrations_between_index_and_attribute_first_appearance[distance] += 1
      end
    else
      num_single_attribute_indexes += 1
      if indexed_attributes.join(",") =~ /_id/
        num_single_attribute_indexes_that_look_like_foreign_keys += 1 
      end
      attribute = indexed_attributes.join(",")
      distance = number_of_migrations_since_first_reference(attribute, directory, file_path.split("/")[-1])
      count_of_migrations_between_index_and_attribute_first_appearance[distance] += 1
    end
  end
  
  puts "Stats for: #{app}"
  puts "Total lines of add_index: #{num_indexes}"
  puts "Total lines w/ multiple attributes: #{num_multi_attribute_indexes}"
  puts "Total lines w/ single attribute: #{num_single_attribute_indexes}"
  puts "Total lines w/ single attribute that don't appear to be a FK: #{num_single_attribute_indexes - num_single_attribute_indexes_that_look_like_foreign_keys}"
  puts "Distribution of # of migrations from first appearance to index: #{count_of_migrations_between_index_and_attribute_first_appearance.inspect}"
  puts "====" * 20
  puts 
end