Go to content
The Codest
  • About Us
  • Services
  • Our Team
  • Case studies
    • Blog
    • Meetups
    • Webinars
    • Resources
Careers Get in touch
  • About Us
  • Services
  • Our Team
  • Case studies
    • Blog
    • Meetups
    • Webinars
    • Resources
Careers Get in touch
2020-06-16
Software DevelopmentStartups

Ruby on Rails software development. Indexes v2

Damian Watroba

Software Engineer

Ruby on Rails software development. Indexes v2 - Image

When working with the Ruby on Rails framework, we usually deal with relational databases such as MySQL or PostgreSQL. When defining migrations using the Active Record Migrations, we come across the so-called indexes, but beginners often do not quite understand indexes and what benefits they bring.

When working with the Ruby on Rails framework, we usually deal with relational databases such as MySQL or PostgreSQL. When defining migrations using the Active Record Migrations, we come across the so-called indexes, but beginners often do not quite understand indexes and what benefits they bring.

In this post, I would like to explain what indexes are, what they are used for and present some good practices on how to employ them.

Database

There are many database engines, and one of the most popular ones are the previously mentioned MySQL, PostgreSQL, Oracle or Microsoft SQL Server. They are all relational databases, which means that all pieces of data are related to each other and stored in tables. Each table row is called a record, and each has its own unique identifier (id). You can check the ranking of the most popular database engines at https://db-engines.com/en/ranking. You will also find some non-relational databases there, such as MongoDB.

Creating an Index

The tables in our databases can have from just a few to several dozen – in extreme cases, up to several hundred – columns. Keep in mind that each table can have an unlimited number of rows. This number does not result directly from the structure of the database and we should always assume that the number of records will increase successively and, as a consequence, our database will grow. Initial assumptions and queries written in existing applications may be great for a small or medium number of records, but over time, when more data arrives, the application's communication with the database ceases to be efficient.

The programmer's role is to write queries to retrieve some data from the table or tables, but the optimal way of processing the query depends on the database engine. Remember that database engines load data from the disk into the memory and then scan it. This means that if many users perform complex operations at the same time, several of them will have to wait their turn because of the lack of resources to implement their searches. That is why relevant indexes are so important.

Wiki: Index – a data structure that increases the speed of performing search operations on a table.

For each index, we need to define keys (for one or many columns) that will be used to search for records in the table. The data in the index will be sorted with the key that has been previously defined, which will significantly speed up the search for data in the table. The simplest example from everyday life is a phone book in which people are sorted by name and surname. It can be said that our index in this case will be the first and last name.

How do you choose the best index key? It is not difficult – just remember a few rules. Create an index based on columns that:

- will often be used in our inquiries (WHERE),

- in combination with each other give a unique value (i.e. one that will indicate exactly one row),

- will be used as so-called connecting columns (JOIN),

- give the most selective keys, i.e. those that return the smallest number of lines when writing a query.

If we already know which keys will be optimal for our table, we can also ask ourselves how many indexes we need. In this case, it is best to know the queries that will refer to our table already at the design stage.

Let's create indexes for specific queries that will appear, but do not write them for each column. Indexes, like tables, need to be stored somewhere, so when we create tables with an index for each column, we must take into account that the amount of space used can increase significantly.

Create Unique Index

Another issue that we need to think about is uniqueness. It is worth spending the extra five minutes on thinking whether our index is really unique. In this way, we tell the query optimizer that it does not have to expect duplicates on the query. For example, email addresses:

# frozen_string_literal: true

class CreateUsers < ActiveRecord::Migration[6.0]
  def change
    create_table :users do |t|
      t.string :email, null: false
    end
    add_index :users, :email, unique: true
  end
end

On the example of the PostgreSQL engine, I will show the difference in query speed on the email column with a unique index and without an index.

1. You can use sample code snippets on your own database to be able to test the example below. First, let's create an empty table with one column:

CREATE TABLE users (
  email varchar
);

2. Let's generate 10,000 records for the test:

DO $
  BEGIN FOR i IN 1..10000 LOOP
    INSERT INTO users values((select 'user' || i || '@example.com'));
  END LOOP; END;
$;

We will use EXPLAIN ANALYZE to check how fast our query will be processed when we want to find a specific user in the database.

EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

Our query forced iteration around the entire table in search of the record that interests us.

This process is called sequential scanning. In this case, reading the entire table and filtering out particular rows is the best way to do the job.

PostgreSQL will filter out the unneeded lines and simply return those that interest us. This is really the best thing to do in this instance. Sequential scanning is not always bad, there are cases where sequential scanning is ideal.

4. Now is the time to check the query already made on the table that has INDEX UNIQUE. Let's set the index and execute the query.

EATE UNIQUE INDEX index\_email on users(email);
EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

This time PostgreSQL took advantage of index scanning because all the columns needed are already in the index.

Selecting only a few lines will be very efficient when using the index. However, if more data is selected, scanning the index and the table will be too time-consuming.

Summary

As you can see, the execution time for a query on a column with an index is much shorter (in the example shown it is a decrease from 1.267 ms to 0.111 ms, so as much as 91.24%!). The most important difference is the way PostgreSQL searches for the record that interests us. In the first case, the database engine had to search the entire table for the record we were needed. In the second, however, the index structure is sorted and unique, hence the engine knew where the record was located, which significantly accelerated the time of the query processing.

In the case of large databases and very complex queries, correctly set indexes can significantly accelerate the work of your application without the need to increase the speed of the machine on which you search the database.

It is worth remembering that creating indexes on each column is not a good practice. Established indexes will speed up the work of the optimizer when searching for data of interest, but at the same time slow down inserting new and updating existing ones.

Read more:

- Need to use the common JS frameworks in your Rails app? Stimulus.js may be an alternative

- Time for a new reality. An era of remote work has started a month ago

- Web App Development: Why is Ruby on Rails a technology worth choosing?

Related articles

Software Development

3 Useful HTML Tags You Might Not Know Even Existed

Nowadays, accessibility (A11y) is crucial on all stages of building custom software products. Starting from the UX/UI design part, it trespasses into advanced levels of building features in code. It provides tons of benefits for...

Jacek Ludzik
Software Development

5 examples of Ruby’s best usage

Have you ever wondered what we can do with Ruby? Well, the sky is probably the limit, but we are happy to talk about some more or less known cases where we can use this powerful language. Let me give you some examples.

Pawel Muszynski
Software Development

Maintaining a Project in PHP: 5 Mistakes to Avoid

More than one article has been written about the mistakes made during the process of running a project, but rarely does one look at the project requirements and manage the risks given the technology chosen.

Sebastian Luczak
Software Development

5 reasons why you will find qualified Ruby developers in Poland

Real Ruby professionals are rare birds on the market. Ruby is not the most popular technology, so companies often struggle with the problem of finding developers who have both high-level skills and deep experience; oh, and by the...

Jakub
Software Development

9 Mistakes to Avoid While Programming in Java

What mistakes should be avoided while programming in Java? In the following piece we answers this question.

Rafal Sawicki
Software Development

A quick dive into Ruby 2.6. What is new?

Released quite recently, Ruby 2.6 brings a bunch of conveniences that may be worth taking a glimpse of.  What is new? Let’s give it a shot!

Patrycja Slabosz

Subscribe to our knowledge base and stay up to date on the expertise from industry.

About us

We are an agile software development company dedicated to empowering our clients' digital transformation projects and ensuring successful IT project delivery.

    United Kingdom - Headquarters

  • Office 303B, 182-184 High Street North E6 2JA London, England

    Poland - Local Tech Hubs

  • Business Link High5ive, Pawia 9, 31-154 Kraków, Poland
  • Brain Embassy, Konstruktorska 11, 02-673 Warsaw, Poland
  • Aleja Grunwaldzka 472B, 80-309 Gdańsk, Poland

    The Codest

  • Home
  • About us
  • Services
  • Case studies
  • Know how
  • Careers

    Services

  • PHP development
  • Java development
  • Python development
  • Ruby on Rails development
  • React Developers
  • Vue Developers
  • TypeScript Developers
  • DevOps
  • QA Engineers

    Resources

  • What are top CTOs and CIOs Challenges? [2022 updated]
  • Facts and Myths about Cooperating with External Software Development Partner
  • From the USA to Europe: Why do American startups decide to relocate to Europe
  • Privacy policy
  • Website terms of use

Copyright © 2022 by The Codest. All rights reserved.

We use cookies on the site for marketing, analytical and statistical purposes. By continuing to use, without changing your privacy settings, our site, you consent to the storage of cookies in your browser. You can always change the cookie settings in your browser. You can find more information in our Privacy Policy.