Introduction

Hello. This is the first post. It is for notes of my study. I'm a beginner, so I think there are some mistakes. I would appreciate it if you could point out in the comments.

Questions

What is the purpose of indexing the table that was often mentioned in the rails tutorial? Was a question. As for my question, when I googled "What is an index?", I got a lot of sentences saying "Add an index as an index because there is a lot of waste when searching data", but this is often the case. I did not know. index? Is it useless to use the index number? I felt that. Specifically, if you have the following code.

Railstutorial 4th Edition (for Rails 5.1) Listing 14.1

`db/migrate/[timestamp]_create_relationships.rb`


class CreateRelationships < ActiveRecord::Migration[5.1]
  def change
    create_table :relationships do |t|
      t.integer :follower_id
      t.integer :followed_id

      t.timestamps
    end
    add_index :relationships, :follower_id
    add_index :relationships, :followed_id
    add_index :relationships, [:follower_id, :followed_id], unique: true
  end
end

Adding an index

Adding an index seems to mean extracting id and a specific column from the original table. I started with the following code

add_index :relationships, :follower_id

I thought it would be to add the index numbers 1,2,3 to the follower_id column in order. Therefore, I was thinking, "Is the index number good for that role?" However, the above code does not mean that, it seems to be an act of ** "creating and saving data that extracts only the follower_id column and id column from the relationships table" **.

Benefits

In other words, if there is no index, when you try to search by follower_id, you have to search the follower_id value one by one from the follower_id column of all tables, and even though you only want the follower_id value, other than the relationships table It means that all columns of will be read as well. By creating an index, you can search using only the columns you want, which improves the search speed.

Disadvantages

However, this is not only a good thing, but it seems that there is a disadvantage that the processing when adding data becomes heavy because the operations that must be processed per data increase, so carefully consider whether it suits the model you want to apply. It seems that it is necessary to implement it.

References

Meaning and advantages of dbonline index Disadvantages | Introduction to SQLite https://www.dbonline.jp/sqlite/index/index1.html

Rails Tutorial/Significance of Indexing