Pit of count method (Be careful of issuing a large number of queries!)

Introduction

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. "

N + 1 problem and its concrete example

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.

users table

Association

• has_many :posts

posts table

Association

• belongs_to :user

Implementation method that causes N + 1 problem

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 tables 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

Implementation method to solve N + 1 problem

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 users 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 users 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

When you get the number of likes with the count method,

Let's get into the main subject. When I get the number of likes for a post@posts each do |post|〜endIn the block ofpost.likes.countI 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.

Solution (1) Use the size method

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)

Solution (2) Add a column (likes_count)

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)

Summary

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.