Work Better With Rails Web Application by Improving Database Indexes

For a very good reason, since past few years, Rails has been a foremost choice for a Ruby on Rails developer or Ruby On Rails Development Company in any kind of Web Application development. For beginners, it gets very easy to learn due to its readable coding methodology and for high skilled ROR developers, its abstractions make easy to build any kind of features in an efficient manner.

There is an unfortunate side effect of such kind of abstract understandings that lead to an especial problem when it comes to databases and SQLs.

Initially, when a Rails app is developed, it is used to be very fast and briskly. But, let say, if products count or users count gets flocked in all together in the Web Application, then web requests gets affected, and the databases CPU usage gets increased to the limit crossed level of height.

What is the reason for such slowing down of Rails Web Application?

At the initial stage of any Web application, due to less traffic, it works very smoothly and fastly. Here comes, Database queries, which plays a very important role in any Web application performance. If these are not managed in a way to handle a huge amount of traffic, then it leads to slowing down of application time to time. Huge traffic causes issues by running N+1 queries and if there is any lack of proper database indexes then it acts like the main culprit to it.

A Database Index is exactly what it sounds like. If we think about indexes present at the end of the book, those are very helpful in quick search of data with the help of pointers present in it. Without an index in the database, the query run gets typical when it finds every row to collect data and perform functionality, that leads to much more consumption of time load. For getting rid of it, adding index greatly speed it up.

Basic example:

While querying the user table, that is a common routining in any application.

Users.where(:email => current_user.email)

Rails automatically add the index to the id field of a database table. Whereas in this query we are asking it to find data record from some another field, here is EMAIL. If we need such kind of records than adding index for that data is mandatory.

class AddEmailIndexToUsers < ActiveRecord::Migration
  def change
    add_index :users, :email, :unique => true
  end
end

Column indexing can be understood like a key in a hash. For large tables, or the tables those are frequently loaded by checking the values in 1 or 2 columns, adding an index to such kinds can provide highly efficient performance without upgrading the queries again and again.

In any table where foreign key exists, it additionally should have an index. When we add belongs_to: photo, Rails does not update the database to index user.photo_id. In such way, by every direction of our model’s user and photo lookup becomes fast and easy.

def up
  add_index(:users, :username)
  add_index(:users, :photo_id)
end

Unique Column Contraints:

Database indexes can also be added by adding uniqueness on the columns. It not only helps in performance improvement rather works like a Rails validator as well.

When not to use Indexes:

By now, we have started thinking to use indexes in each and every database of every Rails Web application Development out there.

Wrong Idea !!

Although indexing works faster with select queries, whereas insert and update work quite slower by maintaining the indexes. However there are only milliseconds changes while any insert queries, but with huge traffic seconds also matters a lot in the application.

Use them wisely:

Indexes on columns of the table will always help in increas of speed, side by side it uses more disk spaces that can be a disadvantage for the Ruby on Rails application Development Company.

Wrapping Up:

Optimizing the SQL queries might not look like such important task to all Web applications built up in the Rails. But if, kept all these points in mind while building the Rails application Development then we can have much more easier time spends while writing queries in our databases(models and migrations), to play with all CRUDs, along with it enjoying much faster-working queries for a long time, no matter what count of traffic comes up altogether on the Web Application. It will never affect the load time of Web App, leading behind the easier way for front-end users to surf and go through the Web App hassle-free.

Leave a Comment