Detect unnecessary indices in your database

extra_indices.rb

require 'rubygems'
require 'active_record'

# Look through each table and look for indexes that are subsets
# of each other.

ActiveRecord::Base.establish_connection(:database => 'elfcast_development', :adapter => 'mysql')
ActiveRecord::Base.connection.tables.each do |table|
  # go through each index
  indexes = ActiveRecord::Base.connection.indexes(table)
  # look through all the indexes and look for subsets
  indexes.each do |index|
    indexes.each do |iindex|
      next if index.name == iindex.name # skip ourself
      if index.columns.slice(0, iindex.columns.count) == iindex.columns
        puts "\e[031m`#{table}`.#{iindex.name}\e[0m is a left subset of \e[032m`#{table}`.#{index.name}\e[0m !!!"
      end
    end
  end
end

elfcast!