I have two models in Flask-SQLAlchemy (Post and Comment) that have many-to-many relationship that is manifested in the third model (post_mentions):
post_mentions = db.Table( 'post_mentions', db.Column('post_id', db.Integer, db.ForeignKey('posts.id'), primary_key=True), db.Column('comment_id', db.Integer, db.ForeignKey('comments.id'), primary_key=True), ) class Post(db.Model): __tablename__ = 'posts' id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String, unique=True, nullable=False) mentions = db.relationship('Comment', secondary=post_mentions, lazy='dynamic') def __eq__(self, other): return self.name.lower() == other.name.lower() def __hash__(self): return hash(self.name.lower()) class Comment(db.Model): __tablename__ = 'comments' id = db.Column(db.Integer, primary_key=True) text = db.Column(db.Text, nullable=False) created_at = db.Column(db.Integer, nullable=False)
There is also a /posts endpoint that triggers the following query:
# flask and other imports @app.route('/posts') def posts(): page_num = request.args.get('page', 1) posts = models.Post.query.join(models.post_mentions)\ .group_by(models.post_mentions.columns.post_id)\ .order_by(func.count(models.post_mentions.columns.post_id).desc())\ .paginate(page=int(page_num), per_page=25) return render_template('posts.html', posts=posts)
There are more than 14k+ posts and 32k+ comments stored in SQLite database. As you can see from the snippet above, when someone hits /posts endpoint, SQLAlchemy loads all data at once to the memory and then subsequent queries (e.g. retrieving posts, comments to that posts, etc..) take sub-millisecond time, since data is being served from the memory without hitting the database. Initial load takes 10s+ on my laptop, which is, to put it mildly, suboptimal.
So the question is: Considering that users won't view 97+% of posts, how can I both order posts by number of mentions in comments and load them on demand instead of doing it in one swoop?