If you are looking to improve your database performance, you might
want to index your database, esspecially if it is large. Here are
some rake tasks that will look at your code and active record and
automatically add indexes to your devel db.
These two tasks are fairly rough drafts. I am open to any input that
would expand on what I have here.
lib/tasks/db_indexing.rake
(then run rake db_indexing:index_fks_in_model and rake
db_indexing:index_sql_calls_in_app )
require ‘ActiveRecord’
require(File.join(RAILS_ROOT, ‘config’, ‘environment’))
namespace :db_indexing do
desc “This task will search text files for rails queries and,
checking for duplicates, generate sql that properly indexes these
commands”
task :index_fks_in_model do
connection = ActiveRecord::Base.connection
connection.tables.each do |table|
begin
table_columns = eval(table.singularize.capitalize +
“.column_names”)
table_columns.each do |column_string|
if column_string.include?('id’)
connection.execute("CREATE INDEX " + table + "" +
column_string + “_index” + “ON” + “#{table}(#{column_string});”)
end
end
rescue
end
end
puts “Task completed”
end
desc “This task will iterate through the objects in your model and
find out which columns end with id and generate an sql commands to
index these columns"
task :index_sql_calls_in_app do
connection = ActiveRecord::Base.connection
controller_files = File.join("", “*_controller.rb”)
helper_files = File.join("", "*helper.rb")
no_of_calls = 0
array_of_files = Dir.glob(controller_files).concat(Dir.glob
(helper_files))
array_of_files.each do |file|
temp_f = File.new(file)
temp_f.each do |line|
if line.to_s.include?(":conditions")
array_of_columns_to_index = []
critical_string = line.to_s.match(’([:][c][o][n][d][i][t][i]
[o][n][s].[=][>].)[.+]’)[0] if line.to_s.match(’([:][c][o][n][d][i]
[t][i][o][n][s].[=][>].)[.+]’)
table = line.to_s.match(’=.+’)[0]
table = table[1, table.index(’.’) - 1]
table = table.chomp.strip
temp_string = ‘’
table.each_char do |char|
if char.downcase.to_s == char.to_s
temp_string = temp_string + char
else
temp_string = temp_string + '’ + char.downcase
end
end
table = temp_string[1, temp_string.length]
table = table.chomp.strip.downcase.pluralize
cs2 = critical_string.to_s.match(’[.+]’)[0] if
critical_string.to_s.match(’[.+]’)
if cs2
cs3 = cs2[1, cs2.index(’,’) - 1] if cs2.index(’,’)
else
end
if cs3
cs3 = cs3[1,cs3.length - 2]
cs3.to_s.each(’ ‘) do |tstr|
tstr = tstr.chomp.strip
tstr = tstr.downcase
if !tstr.match(’\W’) && tstr != ‘and’ && tstr != ‘or’ &&
tstr != ‘is’ && tstr != ‘1’ && tstr != ‘null’ && tstr != ‘0’ && tstr !
= ‘id’
array_of_columns_to_index << tstr
end
end
end
index_name = table.to_s + "”
index_string2 = ‘’
if array_of_columns_to_index.length > 0
array_of_columns_to_index.each do |column_to_index|
index_name = index_name + column_to_index + “_”
index_string2 = index_string2 + column_to_index + ‘,’
end
index_string2 = index_string2[0,index_string2.length - 1]
index_name = index_name + ‘index’
sql_command = "CREATE INDEX " + index_name + " ON " +
table.to_s + “(” + index_string2 + “)” + “;”
begin
puts sql_command
connection.execute(sql_command)
rescue StandardError =>e
puts "ERROR"
if !e.clean_message.to_s.include?(“42000”)
puts e.clean_message.to_s
puts e.backtrace
end
end
end
end
end
end
puts "Task completed"
end
end