rails_legacy_inspector.rb

class LegacyInspector
  def initialize(should_have_data_newer_than=nil)
    @should_have_data_newer_than = should_have_data_newer_than || 6.months.ago
  end

  def connection
    @connection ||= ActiveRecord::Base.connection;
  end

  def tables
    @tables ||= connection.execute('SHOW TABLES;').map(&:first)
  end

  def is_stale?(table)
    begin
      newest_record = connection.execute("SELECT created_at FROM #{table} ORDER BY id DESC LIMIT 1").to_a[0]
      !(newest_record && newest_record[0] >= @should_have_data_newer_than)
    rescue ActiveRecord::StatementInvalid => e
      return nil
    end
  end

  def stale_tables
    tables.select{|table_name| is_stale?(table_name) }
  end

  def non_rails_tables
    tables.select{|table_name| is_stale?(table_name) == nil}
  end

  def unused_indexes_for(table_name, minimum_cardinality=10)
    indexes = connection.execute("show indexes from #{table_name};").to_a
    indexes.select{|index| cardinality = index[6]; cardinality < minimum_cardinality }
  end

  def unused_indexes(tables=nil, minimum_cardinality=10)
    (tables || (self.tables - stale_tables - non_rails_tables)).map do |table|
      (unused = unused_indexes_for(table, minimum_cardinality)).present? ? {table => unused} : nil
    end.compact
  end
end

rarely_updated_tables = ["campaign_categories", "countries"]
legacy_tables = LegacyInspector.new.stale_tables - rarely_updated_tables

non_rails_tables = LegacyInspector.new.non_rails_tables

LegacyInspector.new.unused_indexes

# TODO many_to_many