I'm currently using rails
to create a portfolio for career change activities. Among them, I used a gem called bullet
that detects the N + 1 problem
, but I noticed that it could not be detected and issued a query in vain, so leave the article. I think this. The cause is "I used the count method
to get the total number of likes on the post list page. "
If you know it, please skip it. The N + 1 problem
is a problem that issues more queries than necessary when referencing a column in a table with an association. It puts an extra load on the server side and causes the page to load slowly. As a simple example, consider a situation where the User
model and the Post
model have the following table design, and the post list page outputs the names of all articles and posters.
Column | Type | Options |
---|---|---|
name | string | null: false |
Association
Column | Type | Options |
---|---|---|
user | references | null: false, foreign_key: true |
text | string | null: false |
Association
app/controllers/posts_controller.rb
class PostsController < ApplicationController
def index
@posts = Post.all
end
end
html:app/views/index.html.erb
<% @posts.each do |post|%>
<%= post.user.name %>
<%= post.text %>
<% end%>
With this method, for example, if there are 3 posts, when you call @ posts
, you will get all the records in the posts table
(issue one query) and post with post.user.name
. You have to access as many users table
s as you have (issued 3 queries). This is the reason why it is called the N + 1 problem
.
N+1 Problematic query
Post Load (0.6ms) SELECT `posts`.* FROM `posts` WHERE `posts`.`created_at` BETWEEN '2019-12-17 00:00:00' AND '2020-12-17 23:59:59.999999' ORDER BY `posts`.`created_at` DESC LIMIT 6 OFFSET 0
↳ app/views/posts/_index_posts.html.erb:2
User Load (0.3ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
↳ app/views/posts/_index_posts.html.erb:7
CACHE User Load (0.0ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
↳ app/views/posts/_index_posts.html.erb:7
CACHE User Load (0.0ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
↳ app/views/posts/_index_posts.html.erb:7
Use the preload method
on your controller. You can also use the includes method
to get the same behavior.
app/controllers/posts_controller.rb
class PostsController < ApplicationController
def index
@posts = Post.all.preload(:user)
end
end
This allows you to get the user
s of the association destination all at once when you declare @posts
. This is achieved by splitting the query in two. The first query is a query that retrieves relevant data, the second query is a query that retrieves the final result, and so on. Even if the number of user
s increases, you can get ** SELECT users
. * FROM users
WHERE users
.id
IN (1, 2, 3, ,,,) ** at once. I can do it.
N+1 Query after problem resolution
Post Load (0.3ms) SELECT `posts`.* FROM `posts` WHERE `posts`.`created_at` BETWEEN '2019-12-18 00:00:00' AND '2020-12-18 23:59:59.999999' ORDER BY `posts`.`created_at` DESC LIMIT 6 OFFSET 0
↳ app/views/posts/_index_posts.html.erb:2
User Load (0.3ms) SELECT `users`.* FROM `users` WHERE `users`.`id` = 1
Let's get into the main subject. When I get the number of likes for a post@posts each do |post|〜end
In the block ofpost.likes.count
I was doing like this. Then, as shown below, an extra query to get the total value is generated each time in each. Also, Bullet is N+It will not detect if it is 1.
N by count method+1 Problematic query
↳ app/views/posts/_index_posts.html.erb:2
(0.2ms) SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 7
↳ app/views/posts/_index_posts.html.erb:25
(0.2ms) SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 6
↳ app/views/posts/_index_posts.html.erb:25
(0.2ms) SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 5
↳ app/views/posts/_index_posts.html.erb:25
(0.2ms) SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 4
↳ app/views/posts/_index_posts.html.erb:25
(0.2ms) SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 3
↳ app/views/posts/_index_posts.html.erb:25
(0.2ms) SELECT COUNT(*) FROM `likes` WHERE `likes`.`post_id` = 2
This is because the count method
is the method defined in the Enumerable module
. (All the methods of this module are defined using each
. Enumerable module details Click here ) So, let's look at two ways to solve this problem.
The solution is to change the count method
to the size method
. However, it is not just a matter of changing. You can see this by looking at size method definition .
size method
def size
loaded? ? @records.length : count(:all)
end
This means that if you already have an array loaded, you will get its length (number of elements), otherwise a COUNT query will be executed. The solution is to use the [preload method](## N+1問題を解決する実装方法). This makes the query look like this:
N by size method+1 Query that solved the problem
Like Load (0.3ms) SELECT `likes`.* FROM `likes` WHERE `likes`.`post_id` IN (2, 3, 4, 5, 6, 7)
rails supports the option to have a column with the number of associated models. That is the : counter_cache
option. Use it as follows.
app/controllers/posts_controller.rb
class Like < ApplicationRecord
belongs_to :post, counter_cache: :likes_count
end
This way, you can have a "number of likes" as a column by simply adding a column called likes_count
to the Posts table with a integer type
. Therefore, if you just get and display the value like post.likes_count
, the query will not be issued. (Issued when the number of likes changes)
I have introduced two ways to solve the N + 1 problem caused by using the count method. If you add the column explained at the end, the page will load faster because you do not preload it, but in a situation like "There are many users and you press the like button all at once", on the contrary, a large amount of load will be applied. That is my personal opinion. Also, the : counter_cache
option seems to be prone to deadlocks. There seems to be a way to use a gem called counter_culture
as a way to solve this, so I'd like to try using this as well and write an article soon.