CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
kirqe
kirqe

Reputation: 2470

optimize sql query rails

On posts index page I list all posts this way:

posts_controller.rb

def index
  @posts = Post.includes(:comments).paginate(:page => params[:page]).order("created_at DESC")
end

index.html.erb

 <%= render @posts %>

_post.html.erb

<%= gravatar_for post.user, size:20 %>
<%= link_to "#{post.title}", post_path(post) %>
<%= time_ago_in_words(post.created_at) %> 
<%= post.comments.count %>
<%= post.category.name if post.category %>

35 posts per page

When I first load the page in dev env, rack-mini-profiler shows this time: 1441.1 ms

after a few reloads: ~700 ms

Can I somehow decrease this time and number of sql requests?

Here're rmp images if it helps:

enter image description here

enter image description here

Upvotes: 1

Views: 467

Answers (3)

Jiř&#237; Posp&#237;šil
Jiř&#237; Posp&#237;šil

Reputation: 14412

Further, you don't actually seem to be using the comments collection for anything other than printing the number of records. If that's indeed the case, use counter_cache (4.1.2.3) instead of querying for the comments (the number of comments will be available in the parent record Post).

Also consider a client side alternative to time_ago_in_words. It will also help if you later decide to cache the entire section/page.

And finally retrieve only the fields you're going to use. In this case, I can imagine the Post contains a large amount of text for the content and it's not used anywhere (but still needs to be transmitted from the DB).

Upvotes: 1

Andreii
Andreii

Reputation: 81

Adding an index on the reference column (comments in your case) might help.

add_index :posts, :comment_id

Upvotes: 0

Frederick Cheung
Frederick Cheung

Reputation: 84182

You could decrease the number of sql queries by:

  • including user as well as comments, since you seem to be using that when displaying the gravatar

  • changing post.comments.count to post.comments.size

While size, count, length are synonymous for arrays, for active record relations or associations they are not the same:

  • length loads the association (unless it is already loaded) and returns the length of the array
  • count does a select count(*) query whether the association is loaded or not
  • size uses length if the association is loaded and count if not.

In your case the comments association is loaded, but because you are using count, it's not actually used

Upvotes: 5

Related Questions