Improve like and comment count performance

The table joins generated by the query library for the like and comment counts read 2,500 rows for a single post. It turns out the like count is unused and can be removed. The comment count can be replaced with the array length since the comments are already loaded for display on the post page.

Generated query

select beam.Post.id, beam.Post.title, beam.Post.content, beam.Post.contentHtml, beam.Post.createdAt, beam.Post.hidden, beam.Post.authorId, aggr_selection_0_Comment._aggr_count_comments, aggr_selection_1_LikedPosts._aggr_count_likedBy 
from beam.Post left join (
  select beam.`Comment`.postId, COUNT(*) as _aggr_count_comments 
  from beam.`Comment` group by beam.`Comment`.postId) as aggr_selection_0_Comment
  on beam.Post.id = aggr_selection_0_Comment.postId 
left join (
  select beam.LikedPosts.postId, COUNT(*) as _aggr_count_likedBy 
  from beam.LikedPosts group by beam.LikedPosts.postId
) as aggr_selection_1_LikedPosts
on beam.Post.id = aggr_selection_1_LikedPosts.postId 
where beam.Post.id = ?
limit ?, ?

Correlated subqueries

Another solution is to use correlated subqueries, which reads only 35 rows, but I'm not familiar enough with Prisma to know where to patch that in.

select beam.Post.id, beam.Post.title, beam.Post.content, beam.Post.contentHtml, beam.Post.createdAt, beam.Post.hidden, beam.Post.authorId,
  (select count(*) from beam.Comment where postId=beam.Post.id) as count_comments,
  (select count(*) from beam.LikedPosts where postId=beam.Post.id) as count_likes
from beam.Post where id = ?

planetscale / beam

Improve like and comment count performance #64

Generated query

Correlated subqueries