Database rake tasks that I use on Code School to ferret out huge tables with millions of rows and see how many indices they have and to see which tables have missing indices on associated tables (foreign keys). The latter was taken from this great post by Tom Ward: https://tomafro.net/2009/09/quickly-list-missing-foreign-key-indexes

database.rake

namespace :database do

  task fat_tables: :environment do
    c = ActiveRecord::Base.connection

    max_table_name_width = 0

    tables = c.tables.sort_by do |t|
      max_table_name_width = t.length if t.length > max_table_name_width

      c.execute("SELECT count(*) FROM #{t}").values.first.first.to_i
    end

    tables.reverse.map do |t|
      row_count = c.execute("SELECT count(*) FROM #{t}").values.first.first.to_i
      index_count = c.indexes(t).map(&:columns).flatten.uniq.count

      # `\t` is a tab character (8 spaces because Unix is cuh-razy!)
      # the + 1 is a magic number for padding, sue me
      tabs_required = "\t" * (((max_table_name_width - t.length) / 8).ceil + 1)

      puts "#{t}: #{tabs_required} #{row_count} rows \t #{index_count} indices"
    end
  end

  task missing_keys: :environment do
    c = ActiveRecord::Base.connection
    c.tables.map do |t|
      columns = c.columns(t).map(&: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
  end

end

fat_tables_example_output

table1: 51054374 rows 	 1 indices
table2: 5294615 rows 	 4 indices
table3: 4068456 rows 	 2 indices
table4: 3545259 rows 	 2 indices
table5: 3464336 rows 	 3 indices
table6: 1952531 rows 	 4 indices
table7: 1034054 rows 	 7 indices
table8: 932215 rows 	 1 indices
table9: 507774 rows 	 2 indices

missing_keys_example_output

table1: table2_id
table2: table3_id, table1_id
table3: table1_id
table4: table3_id

What database server you're using?

What database server you're using?