Forum: Ruby on Rails Some tasks that will help index your db

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Da238874c828de5eb3b9651874b75878?d=identicon&s=25 Ben (Guest)
on 2009-01-06 00:34
(Received via mailing list)
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
9a2a53db8e9b4476038c94a64b32833f?d=identicon&s=25 Ryan Bigg (ryan-bigg)
on 2009-01-06 01:07
(Received via mailing list)
[:][c][o][n][d][i][t][i][o][n][s].[=][>].

Er, what? Why not just match for :conditions?
-----
Ryan Bigg
Freelancer
http://frozenplague.net
Da238874c828de5eb3b9651874b75878?d=identicon&s=25 Ben (Guest)
on 2009-01-06 01:35
(Received via mailing list)
If you have a suggestion for the replacing the line of code, I'll test
it.  Regular expressions aren't my forte.
This topic is locked and can not be replied to.