Take the 2-minute tour ×

I have an app with users, relationships, posts, likes.
My models are:

class User
  has_many :posts
  has_many :likes
  has_many :relationships, :foreign_key => "follower_id", :dependent => :destroy

class Post
  belongs_to :user
  has_many :likes  

class Like
  belongs_to :user
  belongs_to :post

class Relationship
  belongs_to :follower, :class_name => "User"
  belongs_to :followed, :class_name => "User"

So I want to find at least 100 users who likes my current post:

friends = User.find(user.followers).likes.where(:post => @post, :limit => 100)

It's a simple but not optimized query if there are a lot of users, posts, likes, etc. in DB.
How can I optimize the query (or models) to increase speed and to decrease query's time execution?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

Well, the first thing to do is to make sure you have proper indexes on all your tables.

So there should be an index on all the primary and foreign keys used to join the tables. Then of course you want indexes on any fields in these tables that your might use for sorts or filters.

Outside of that, I don't really see any problem with your database schema.

If, however you want look at non-relational database, a lot of developers are using NoSQL storage for problems such as these where you have one main post, but there may be any number of likes, comments, etc. on it. It is really easy to maintain a single NoSQL document entry in say JSON that contains the entire tree structure for a single post, rather than having to assemble this information from disparate table in a relational DB structure.

share|improve this answer
Thanks, Mike. I think NoSQL+MySQL is just that I need! I want to save info about posts into NoSQL and update info (add user_id) then user likes post. To select users who liked post I'll run SELECT query on MySQL users table. Do you think it's good workaround? –  wiseland Sep 11 '12 at 16:01

I agree with Mike. Your schema looks good, but you should add some indices.

If you actually run into performance problems, your best option would be to denormalize some of your data (i.e. pre-compute some queries and cache the result).

An obvious candidate for caching would be storing a count of "Likes" for a particular user or post. You could update this every time someone clicks "Like", or you could only occasionally update the counts through a cron job or something like that. Then you'd be able to quickly report that "234 people like this" without actually running the JOIN query. It's possible that the stored count could get out-of-sync if you only sometimes re-calculate it, but that's not really a big deal for this application (it's not like it's a bank account balance!).

share|improve this answer
In Rails, you can create the appropriate database field and set :counter_cache => true in your relationship declaration to take care of this. Link to relevant RailsCast for more information. –  niiru Sep 10 '12 at 17:24

Have Indexes and also try using eager loading. Something like

users = User.includes(:likes => [:post]).find(user.followers)
friends = users.where(:post => @post).limit(100)

When you have loads of data use the find_in_batches which will save memory consumption as Activerecord memory will be released per batch transaction

share|improve this answer

Your Answer


