mysql-php, according to the article count the number of comments to sort, how the fastest way?

The current is to take all the data out, reorder, very slow. And now the paging is required.

Is there a better way to do this without considering the total number of comments? Can a native SQL statement be done directly?

Reply content:

The current is to take all the data out, reorder, very slow. And now the paging is required.

Is there a better way to do this without considering the total number of comments? Can a native SQL statement be done directly?

SELECT     posts.id,     posts.content,     comments.id AS comment_id,    comments.body,    count(comments.id)FROM postsLEFT 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页

1. Add a field in the article table called the number of comments, and then each time you have this article new comments add it 1, and then sort by this field

2. If you only take the sort of article ID, you can do it in the comment form:

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

Which post_id is the article ID, comment is the comment table, this statement to get the most number of comments the top 10 article ID

