Rails Tutorial/Significance of Indexing

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

Recommended Posts

Rails Tutorial/Significance of Indexing
[Rails] Introduction of PAY.JP
Rails implementation of ajax removal
[Rails] Introduction of devise Basics
Utilization of Rails Boolean type
[Rails 6] Implementation of search function
[Rails] Implementation of category function
[Rails] Implementation of tutorial function
[Rails] Implementation of like function
[Rails Struggle/Rails Tutorial] Summary of Rails Tutorial Chapter 2
[Rails] Implementation of user logic deletion
[Rails] Implementation of CSV import function
[Rails] Asynchronous implementation of like function
[Rails] List instances of multiple models
[Rails] Introduction of Rubocop by beginners
[Rails] Check the contents of the object
[Rails] Implementation of image preview function
Basic knowledge of Ruby on Rails
Kaminari --Added pagination function of Rails
[Rails] About implementation of like function
[Rails] Implementation of user withdrawal function
[Rails] Implementation of CSV export function
[Rails] Summary of complicated routing configurations
Check the migration status of rails
Japanese localization of error messages (rails)
[Rails] Implementation of many-to-many category functions
[Rails] Differences and usage of each_with_index and each.with_index
[Rails] Types of associations (one-to-many / many-to-many)
[Specific usage of before_action] Rails refactoring
From pulling docker-image of rails to launching
[Ruby on Rails] Introduction of initial data
[Rails] Addition of Ruby On Rails comment function
[Rails] Temporary retention of data by session
The identity of params [: id] in rails
Rails Addition of easy and easy login function
Let's summarize "MVC" of Ruby on Rails
part of the syntax of ruby ​​on rails
[Rails Struggle/Rails Tutorial] Summary of Heroku commands
[rails] List of actions defined in Controller
[Rails 6.0] About batch saving of multiple records
Item 37: Use EnumMap instead of ordinal indexing
[Rails] Change the label name of f.label
Rails [For beginners] Implementation of comment function
[Rails 6] Implementation of SNS (Twitter) sharing function
[Ruby on Rails] Japanese notation of errors
Rails Basics of creating a new application
Explanation of Ruby on rails for beginners ①
[Vue.js] Implementation of menu function Implementation version rails6
The process of introducing Vuetify to Rails
[Ruby on rails] Implementation of like function
[Rails] Implementation of validation that maintains uniqueness
[Vue.js] Implementation of menu function Vue.js introduction rails6