How to organize query to DB if I have a lot of users, posts, likes
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 end class Post belongs_to :user has_many :likes end class Like belongs_to :user belongs_to :post end class Relationship belongs_to :follower, :class_name => "User" belongs_to :followed, :class_name => "User" end
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?
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.