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? JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

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