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