I am working in a lot having a Large mysql2 database right now, and although I've indexed things i thought were the correct fields, the return time for many queries is very slow.

I've two models which are leading to issues, Comment and Commenter.

Now, there's a has_many relationship between Commenter and Comment, but my query depends on locating the each Comment's Commenter's username. So I'll run something similar to this:

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

To assist using the speed issues, I produced a catalog around the commenter_id area for that Comment model. But it's still running very slow..

Does anybody are conscious of things i could do in a different way, that will aid in increasing the rate from the query?

A catalog on commenter_id helps when you wish to obtain the comments for any given commenter_id ("find me all of the comments joe made").

However when you do c.commenter you are trying to find customers, most probably the main one whose id is equivalent to the comment's commenter_id. There should be a catalog around the id column. The guaranteed strategy is to accept actual sql claims produced (in development they are in development.log), and employ explain in it, for instance

explain select * from comments where id = 12345

Considering that it is extremely unlikely that you simply handled to produce a table with no index on it's id column, probably the most likely reason is eager loading - if your publish had 500 comments then your above code would fetch the connected customers 1 by 1, and individuals 500 roundtrips towards the database accumulate

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


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

will fix that (the above mentioned clips assume you are using rails 3).

Collect will load ActiveRecord objects for each commenter with all of fields individually. Including / Eager Loading will load all of them with one query which supports with speed, but when you would like the very best performance and you simply require the names you are best reaching lower to SQL more directly with something towards 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)

To begin with you must as well much unnecessary queries here c.collect {|c| c.commenter.username} I believe eager loading will help you. Watch this http://railscasts.com/episodes/23-counter-cache-column?autoplay=true

I believed it using eager load, which isn't the hyperlink RaskolnikOFF published, however the rails-cast before it, http://railscasts.com/episodes/22-eager-loading (still gave an upvote for driving me towards the answer)

Apparently things i was searching for was the next:

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

The very first line loads the very first blog and every one of its relations (and returns your blog). Then when I call the 2nd line, things are already loaded and waiting to become utilized..

Which works much better than I had been initially doing.