There will be many reasons behind an application’s slowness, database queries usually play the biggest role in an application’s performance footprint. Loading too much data into memory, N+1 queries, lack of cached values, and the lack of proper databases indexes are the biggest culprits that can cause slow requests.
What is indexing?
It’s almost like looking through the entire table with the human eye – very slow and not at all sleek. But, as you probably guessed by the title of this article, this is where indexes can help a great deal. The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined.
Missing database indexes on foreign keys and commonly searched columns or values that need to be sorted can make a huge difference. The missing index is an issue that is not even noticeable for tables with several thousand records. However, when you start hitting millions of records, the lookups in the table become painfully slow.
When you create a database column, it’s vital to consider if you will need to find and retrieve records based on that column.
Example
For example, let’s take a look at a demo project. We have a Project model, and every project has a name attribute. When someone visits a project on demo project, e.g. https://demo.com/projects/text-finder, the first thing we need to do in the projects controller is to find the project based on its name — text-finder.
project = Project.find_by_name(params[:name])
Without an index, the database engine would need to check every record in the projects table, one by one, until a match is found.
Quick Fix
However, if we introduce an index on the ‘projects’ table, as in the following example, the lookup will be much, much faster.
classAddIndexToProjects < ActiveRecord::Migration defchange add_index :projects, :name end end
A good way to think about indexes is to imagine them as the index section at the end of a book. If you want to find a word in a book, you can either read the whole book and find the word, or your can open the index section that contains a alphabetically sorted list of important words with a locator that points to the page that defines the word.
What needs to be indexed?
A good rule of thumb is to create database indexes for everything that is referenced in the WHERE, HAVING and ORDER BY parts of your SQL queries.
Indexes for unique lookups
Any lookup based on a unique column value should have an index.
If you have belongs_to or has_many relationships, you will need to index the foreign keys to optimize for fast lookup.
For example, we have the branches that belong to projects:
classProject < ActiveRecord::Base has_many :branches end
classBranch < ActiveRecord::Base belongs_to :project end
For fast lookup, we need to add the following index:
add_index :branches, :project_id
Determination
While using indexes for important fields can immensely improve the performance of your application, sometimes the effect can be negligible, or it can even make your application slower.
For example, tables that have elements that are frequently deleted can negatively impact the performance of your database. Huge tables with many millions of records also require more storage for your indexes.
Importance of Indexing in rails
There will be many reasons behind an application’s slowness, database queries usually play the biggest role in an application’s performance footprint. Loading too much data into memory, N+1 queries, lack of cached values, and the lack of proper databases indexes are the biggest culprits that can cause slow requests.
What is indexing?
It’s almost like looking through the entire table with the human eye – very slow and not at all sleek. But, as you probably guessed by the title of this article, this is where indexes can help a great deal. The whole point of having an index is to speed up search queries by essentially cutting down the number of records/rows in a table that need to be examined.
Missing database indexes on foreign keys and commonly searched columns or values that need to be sorted can make a huge difference. The missing index is an issue that is not even noticeable for tables with several thousand records. However, when you start hitting millions of records, the lookups in the table become painfully slow.
When you create a database column, it’s vital to consider if you will need to find and retrieve records based on that column.
Example
For example, let’s take a look at a demo project. We have a Project model, and every project has a name attribute. When someone visits a project on demo project, e.g. https://demo.com/projects/text-finder, the first thing we need to do in the projects controller is to find the project based on its name — text-finder.
project = Project.find_by_name(params[:name])
Without an index, the database engine would need to check every record in the projects table, one by one, until a match is found.
Quick Fix
However, if we introduce an index on the ‘projects’ table, as in the following example, the lookup will be much, much faster.
class AddIndexToProjects < ActiveRecord::Migration
def change
add_index :projects, :name
end
end
A good way to think about indexes is to imagine them as the index section at the end of a book. If you want to find a word in a book, you can either read the whole book and find the word, or your can open the index section that contains a alphabetically sorted list of important words with a locator that points to the page that defines the word.
What needs to be indexed?
A good rule of thumb is to create database indexes for everything that is referenced in the WHERE, HAVING and ORDER BY parts of your SQL queries.
Indexes for unique lookups
Any lookup based on a unique column value should have an index.
For example, the following queries:
User.find_by_username(“james“)
User.find_by_email(“james@example.com“)
will benefit from an index of the username and email fields:
add_index :users, :username
add_index :users, :email
If you have belongs_to or has_many relationships, you will need to index the foreign keys to optimize for fast lookup.
For example, we have the branches that belong to projects:
class Project < ActiveRecord::Base
has_many :branches
end
class Branch < ActiveRecord::Base
belongs_to :project
end
For fast lookup, we need to add the following index:
add_index :branches, :project_id
Determination
While using indexes for important fields can immensely improve the performance of your application, sometimes the effect can be negligible, or it can even make your application slower.
For example, tables that have elements that are frequently deleted can negatively impact the performance of your database. Huge tables with many millions of records also require more storage for your indexes.