Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
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
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
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
table1: table2_id
table2: table3_id, table1_id
table3: table1_id
table4: table3_id

What database server you're using?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment