Mysql-php is sorted by the number of comments in the article. what is the fastest way?

Currently, it is slow to extract and sort all data. Besides, pagination is required. Is there a better way to add the total number of comments without considering this field? Can native SQL statements be completed directly? Currently, it is slow to extract and sort all data. Besides, pagination is required.

Is there a better way to add the total number of comments without considering this field? Can native SQL statements be completed directly?

Reply content:

Currently, it is slow to extract and sort all data. Besides, pagination is required.

Is there a better way to add the total number of comments without considering this field? Can native SQL statements be completed directly?

SELECT posts. id, posts. content, comments. id AS comment_id, comments. body, count (comments. id) FROM postsLEFT JOIN comments -- left join. ensure that no comments in the left posts table are displayed ON posts. id = comments. post_id group by posts. id -- group, combined with count (comments. the number of comments in each article is calculated by order by count (comments. id) DESC, posts. id DESC -- sort by number of comments. if the number is the same, sort by article ID. LIMIT 2 OFFSET (3-1) * 2 -- pagination. 2 indicates the number of articles displayed on each page. 3 indicates 3rd pages.

1. Add a field in the article table, which is called the number of comments. each time a new comment is posted in this article, add 1 to it, and sort the comment based on this field.

2. If you only sort the document IDs, you can do the following in the comment table:

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

Wherepost_idIs the article ID,commentIs the comment table. this statement obtains the ID of the first 10 articles with the most comments.