Rails database performance tuning

Question

I'm working a lot with a VERY large mysql2 database at the moment, and although I have indexed what I thought were the proper fields, the return time for some queries is quite slow.

I have two models that are causing issues, Comment and Commenter.

Now, there is a has_many relationship between Commenter and Comment, but my query relies on finding the each Comment's Commenter's username. So I'll run something like this:

c = Blog.first.comments ##this bit runs fine, I indexed the "blog_id" field on Comments
c.collect {|c| c.commenter.username}

To help with the speed issues, I created an index on the commenter_id field for the Comment model. But it is still running very slow..

Does anybody know of what I could do differently, that would help increase the speed of the query?

Benoit Garret · Accepted Answer · 2011-12-19 18:45:25Z

3

An index on commenter_id helps when you want to find the comments for a given commenter_id ("find me all the comments joe made").

But when you do c.commenter you're searching for users, presumably the one whose id is equal to the comment's commenter_id. There should already be an index on the id column. The surefire way is to take the actual sql statements generated (in development these are in development.log), and use explain on them, for example

explain select * from comments where id = 12345

Given that it's very unlikely that you managed to create a table without an index on it's id column, the most likely culprit is eager loading - if a post had 500 comments then the above code would fetch the associated users one by one, and those 500 roundtrips to the database add up

c.includes(:commenter).collect {...}

or

c.eager_load(:commenter).collect {...}

will fix that (the above snippets assume you're using rails 3).

edited Dec 19, 2011 at 18:45

Benoit Garret

13.7k4 gold badges62 silver badges64 bronze badges

answered Dec 19, 2011 at 16:08

Frederick Cheung

84.3k9 gold badges157 silver badges175 bronze badges

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

2 Comments

BananaNeil Over a year ago

I believe eager_load and includes work for only active record relations. Not arrays, and in my post, c is an array.

Frederick Cheung Over a year ago

It's not actually - associations are relations - you can definitely do filtered_ordered_comments = some_blog.comments.where(...).order(...) for example

codatory · Accepted Answer · 2011-12-19 18:17:56Z

2

Collect is going to load ActiveRecord objects for every commenter with all fields one at a time. Including / Eager Loading will load them with one query which will help with speed, but if you want the absolute best performance and you just need the names you're better off reaching down to SQL more directly with something to the tune of:

c         = Blog.first.comments
user_ids  = c.collect(&:commenter_id)
usernames = Commenter.where(['commenter_id IN (?)',user_ids]).select('username').collect(&:username)

answered Dec 19, 2011 at 18:17

codatory

6864 silver badges5 bronze badges

Comments

RaskolnikOFF · Accepted Answer · 2011-12-19 12:29:29Z

1

First of all you do too much unnecessary queries here c.collect {|c| c.commenter.username} I think eager loading can help you. Watch this http://railscasts.com/episodes/23-counter-cache-column?autoplay=true

answered Dec 19, 2011 at 12:29

RaskolnikOFF

3063 silver badges9 bronze badges

Comments

BananaNeil · Accepted Answer · 2011-12-20 02:40:36Z

0

I figured it out using eager load, which is not the link RaskolnikOFF posted, but the rails-cast before it, http://railscasts.com/episodes/22-eager-loading (still gave you an upvote for driving me to the answer)

Apparently what I was looking for was the following:

b = Blog.find(:first, :include=>{:comments => :commenter})
b.comments.collect {|c| c.commenter.username}

The first line loads the first blog and all of its relations (and returns the blog). So when I call the second line, everything is already loaded and waiting to be accessed..

Which works way better than what I was originally doing.

edited Dec 20, 2011 at 2:40

answered Dec 19, 2011 at 20:51

BananaNeil

10.8k9 gold badges52 silver badges69 bronze badges

Collectives™ on Stack Overflow

Rails database performance tuning

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related