mysql - php, what is the fastest way to sort articles based on the number of comments counted?

Currently, it is taking out all the data and sorting it again, which is very slow. And now it requires paging.

Is there a better way without considering the field of adding the total number of comments? Can native sql statements be completed directly?

Reply content:

Currently, it is taking out all the data and sorting it again, which is very slow. And now it requires paging.

Is there a better way without considering the field of adding the total number of comments? Can native sql statements be completed directly?

<code>SELECT 
    posts.id, 
    posts.content, 
    comments.id AS comment_id,
    comments.body,
    count(comments.id)
FROM posts
LEFT JOIN comments --左连接,保证左表posts中没有评论的文章也显示出来
ON posts.id = comments.post_id 
GROUP BY posts.id --分组,配合count(comments.id)统计到每篇文章的评论数
ORDER BY count(comments.id) DESC, posts.id DESC --按评论数排序,相同则按文章ID排序
LIMIT 2 OFFSET (3-1)*2 --分页,2表示每页显示的文章数量,3表示第3页</code>

1. Add a field in the article table called the number of comments, and then add 1 to it every time there is a new comment on this article, and then sort according to this field

2. If you only want to sort by article ID, you can do this in the comment table:

<code>select post_id from comment group by post_id order by count(1) desc limit 0, 10;</code>

Among them post_id is the article ID, comment is the comment table, this statement gets the top 10 article IDs with the most comments