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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
What database server you're using?