Ruby And SQL Wizardly Quest

SQL indexes Basics: HowTo with rails

Lucas BARRET's photo
Lucas BARRET
·

5 min read

Introduction

Indexes are something that you have most probably heard of and use in your app if you use a relational database in it.

In this article, I will dive into the basic of indexes in SQL, and how we can create one index on data on our database with rails and SQL. But also presents the 2 main indexes out there that are

What are indexes?

Advantages

Indexes enable you to speed up specific requests or specific parts of a request you do in SQL. This will create a particular data structure like a tree for the B-tree or B+tree indexes for example. This data structure actually contains sorted values that will enable you to find the pointer to the unsorted data on your disk.

Drawback

Nothing comes for free, in software engineering, it is always a question of tradeoff. The data structure that represents your unsorted data in a sorted way has to be updated at each: insertion and deletion you make in your database.

So inevitably you will slow down these operations, so the use indexes wisely. More your indexes will be complex more it will be costly to update it when you insert and deletes data in your database.

B-Trees vs B+Trees

B-Trees are trees that are just basic trees with leaves that contain pointers to the actual data on your disk.
B+Trees on the other hand, on the end nodes/leaves we point to the next and the previous value if there is any. So we can even speed up the request that required values greater than value or lesser than a value.

For example, let's imagine we have defined B-Trees indexes on our table and we have this query :

SELECT * FROM my_table WHERE my_value > 9

The B-Tree index will not be used whereas if you use the B+Tree index we will be able to use our index and speed up our request. Indeed here when we will found the first value that is greater than in our database then we can go to the previous and next and verify if it is also the case and so on for the other values.

Creating and Using Indexes with rails

How to use the index on your data in rails?

To create an index in SQL you can do that :

CREATE INDEX my_index ON my_table(my_column)

It might surprise you but you already have created indexes in rails and SQL even if you did not have used this.

In rails the first way to create an index is this :

class CreateCoffees < ActiveRecord::Migration[7.0]
  def change
    create_table :coffees do |t|
      t.string :name
      t.string :description
      t.string :origin

      t.timestamps
    end
  end
end

Indeed when you create a table an index is automatically created on the primary key.

Then you have another way to create an index by specifically asking for it :

class AddRoastedCoffeeToCoffees < ActiveRecord::Migration[7.0]
  def change
    add_reference :coffees, :roasted_coffees, foreign_key: true
  end
end

This will automatically create an index on the new reference you are adding on the coffee table for example here.

Little tips when creating a reference sometimes the name of the index will be too long. Indeed the index name derives from the columns of the tables that are used and referenced.

So to fix that you can create an index and specify the name like the following :

class AddRoastedCoffeeToCoffees < ActiveRecord::Migration[7.0]
  def change
    add_reference :coffees, :roasted_coffees, name: 'roasted_index' ,foreign_key: true
  end
end

And finally, you can create an index with the following line of code :

class AddIndexLocationPrice < ActiveRecord::Migration[7.0]
  def change
    add_index :coffees, [:location,:price], order: {location: :asc, price: "DESC NULLS LAST"}), using: 'btree', name: 'index_location_price'
  end
end

This is equivalent the equivalent to

CREATE INDEX index_location_price ON coffees(location ASC, PRICE DESC NULLS LAST) USING BTREE

So here there are several things that we are doing, first, we are creating the index 😎, but this index is on 2 different columns. It is something that I did not talk about in the first part.

This will speed up 2 kinds of queries :

  • Query with a where clause on location

  • Query with a where clause first on location then on price. Only in this order

Tips

Like everything in software engineering try to not forecast too much your index.
Nevertheless, dates are often good candidates for indexes. Because you often filter things by date. Especially when you are doing analytics creating indexes on a date can be really helpful.

Partial index

Then you have the partial index, a partial index is an index that is only applied to a subset of your database. For example, you know that most of the time someone wants to see a coffee from your database they want to see the Ethiopian variety. So you can create an index for this one in particular.

You can create a partial index in rails really easily like this :

class AddIndexLocationPrice < ActiveRecord::Migration[7.0]
  def change
    add_index :coffees, :name, where:"origin = Ethiopia"
  end
end

This is similar

CREATE INDEX ethiopian_coffees on coffees (name)
WHERE origin = 'Ethiopia'

Conclusion

As you can see indexes are pretty well supported in Rails. ActiveRecord is an enjoyable and complete ORM. There is plenty of things about indexes that I did not tackle like indexes on function or deletion of indexes.
Also, indexes are not the panacea of your performance issue, of course, they will speed up your request for data. At the cost of the insertions, updates, and deletions.
In addition, the more your index is complex the more it will slow down those operations.

So when creating an index be careful that it is improving the performance of your database overall and not only for a specific query.

SQLRailsRuby