optimize sql query rails

Question

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

Frederick Cheung · Accepted Answer · 2015-01-03 15:53:57Z

5

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

answered Jan 3, 2015 at 15:53

Frederick Cheung

84.3k9 gold badges157 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kirqe Over a year ago

650 ms :) Is this all I can do for now? I'm also planning to use caching in the future.

Jiří Pospíšil · Accepted Answer · 2015-01-03 17:03:15Z

1

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

edited Jan 3, 2015 at 17:03

answered Jan 3, 2015 at 16:54

Jiří Pospíšil

14.4k2 gold badges44 silver badges52 bronze badges

Comments

Andreii · Accepted Answer · 2015-01-03 15:56:43Z

0

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

add_index :posts, :comment_id

answered Jan 3, 2015 at 15:56

Andreii

811 silver badge7 bronze badges

Collectives™ on Stack Overflow

optimize sql query rails

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related