Rails Rake task to add indexes on columns that are foreign keys

index.rake

  # Rails Rake task to search all database tables and add indexes on columns that are foreign keys, or match the "indexed_columns" variable

  desc 'Add missing indexes'
  task :index_tables => :environment do 
    indexed_columns = ['code', 'name', 'updated_at', 'created_at']
    tables = ActiveRecord::Base.connection.select_values('show tables')
    tables.each do |t|
      columns = ActiveRecord::Base.connection.select_values("describe #{t}")
      keys = columns.select{|k| k.match(/_id$/) || indexed_columns.include?(k)}
      indexes = []
      ActiveRecord::Base.connection.execute("show indexes in #{t}").each do |i|
        indexes << i[4]
      end
      create_indexes = keys - indexes
      inserts = create_indexes.map{|i| "CREATE INDEX #{i} ON #{t} (#{i})"}
      puts " * Creating indexes on #{t}"
      puts  "   - Found: #{indexes.sort.join(', ')}"
      if inserts.empty?
        puts "   - No indexes will be added"
      end
      inserts.each do |i|
        print "   - " + i
        begin
          ActiveRecord::Base.connection.execute(i)
        rescue => e
          puts "ERROR!"
        end
        puts
      end
    end
  end