PHP - Most efficient way to query MySQL for latest comments + total number of comments

On Facebook, when a wall post has a lot of comments, you see only the last two comments. Right above them, however, is a link that says something like "View all 15 comments."

In MySQL, would this require two queries? One to get the total comment count, and the other to get the contents of the last two comments? Then, clicking that "View all" link requires a third in order to get the content of the remaining comments.

If this is the case, wouldn't it be more efficient to simply get the contents of all the comments in a single MySQL query? This would allow you to find the total number using PHP, and you wouldn't need to make the third query if you simply output the contents of all the comments to the page, using css to hide those that you don't want initially shown and JavaScript to show them when the user clicks the "View All" link.

I assume I'm wrong. Surely the Facebook developers are better at this than I am. But I'm developing a site that has a similar requirement and I'm trying to understand the most efficient way to achieve this kind of functionality.

This question and answers originated from www.stackoverflow.com
Question by (4/19/2011 6:15:44 PM)

Answer

In MySQL you could use a limited select to retrieve the latest comments specifying SQL_CALC_FOUND_ROWS followed by a FOUND_ROWS() query to fetch the count of all comments.

ie. First do :

select SQL_CALC_FOUND_ROWS title, description from comments order by post_date desc limit 2;

to retrieve the 2 most recent comments, then do :

select FOUND_ROWS();

to retrieve the total number of comments.

Refer to the FOUND_ROWS() documentation on mysql.com for more information.

As far as performance is concerned, it should be faster then executing another COUNT(*) query (according to the docs :p)...

Answer by
Find More Answers
Related Topics  php  mysql  performance  query
Related Questions