# Some tasks that will help index your db

**URL:** <https://www.ruby-forum.com/t/some-tasks-that-will-help-index-your-db/156978>\
**Category:** Rails\
**Created:** [January 6, 2009, 12:34am UTC](https://www.ruby-forum.com/t/some-tasks-that-will-help-index-your-db/156978 "2009-01-06T00:34:31Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\
**Post date:** [January 6, 2009, 12:34am UTC](https://www.ruby-forum.com/t/some-tasks-that-will-help-index-your-db/156978/1 "2009-01-06T00:34:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\
**Post date:** [January 6, 2009, 1:07am UTC](https://www.ruby-forum.com/t/some-tasks-that-will-help-index-your-db/156978/2 "2009-01-06T01:07:52Z")

</div>

[:][c][o][n][d][i][t][i][o][n][s].[=][\>].

## Er, what? Why not just match for :conditions?

Ryan B.  
Freelancer

> **[frozenplague.net](http://frozenplague.net)**
>
> This domain may be for sale!

---

<div class="post-metadata">

**Author:** ![ben](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/df788c/32.png) [@ben](https://www.ruby-forum.com/u/ben)\
**Post date:** [January 6, 2009, 1:35am UTC](https://www.ruby-forum.com/t/some-tasks-that-will-help-index-your-db/156978/3 "2009-01-06T01:35:21Z")

</div>

If you have a suggestion for the replacing the line of code, I’ll test  
it. Regular expressions aren’t my forte.