Missing indexes on database tables causes performance issues. lol_dba gem helps finding out missing indexes on database table in Rails code. lol_dba performs static analysis of the code to find out missing indexes. We will take a look at how to use lol_dba to find out indexes. lol_dba also helps creating a migration to add the requred indices.

Note

The missing indexes suggested by lol_dba is just a guideline. It suggests which columns should probably be indexed. It is not mandatory to add indices on the suggested tables.

lol_dba can be used just as a gem and not adding it to Gemfile.

Install lol_dba

  gem install lol_dba

You can add it to Gemfile. This way, it will expose rake tasks.

  # Gemfile
  gem 'lol_dba'

Find indexes

Once gem is installed, we can find missing indexes using the following command:

lol_dba db:find_indexes

With Rails

bundle exec rake db:find_indexes

This will output a migration with add_index statements for the database tables with column name.

* TIP: if you have a problem with the index name('index name too long') you can solve with the :name option. Something like :name => 'my_index'.
* run `rails g migration AddMissingIndexes` and add the following content:

    class AddMissingIndexes < ActiveRecord::Migration
      def change
        add_index :activities, :form_id
        add_index :activity_histories, :activity_id
      end
    end

Perform the migration

Figure out relevant indices for the application by looking at the suggested indices by lol_dba. Then, run the migrations to finetune performance of the Rails application.

Notes

• Primary key is always indexed. It is not considered by lol_dba while suggesting indices.

• The gem is created with the help of rails_indexes and migration_sql_generator. Migration SQL Generator gem can be used to create SQLs from migrations.

Generate SQLs from migrations:

• Generate SQLs from migrations with the help of rake task.

lol_dba db:migrate_sql

• Generate SQLs for only pending migrations.

lol_dba db:migrate_sql[pending]

• Generate SQLs for only for the migration with a version number

lol_dba db:migrate_sql[20120221205526]

Summary

When fine tuning the performance with Ruby on Rails application, lol_dba comes in handy to find out which database tables need indices. This can be used as a guideline to add missing indexes and improve performance of the application.