2 comments

1. One of the main problems I see is with the inner query, SELECT *, @num... because it results in a full table scan which will cause MySQL not to use the comment_approved index.

   The reason for this is simply because if you look at the query, it is essentially telling MySQL to select every row in the table ordered by the post ID with no limit.

   mysql> explain SELECT *, @post_id := comment_post_ID as cpID FROM wp_comments WHERE comment_approved = 1 ORDER BY comment_post_ID DESC, comment_ID DESC LIMIT 10;
   +----+-------------+-------------+------+---------------------------+------+---------+------+------+-----------------------------+
   | id | select_type | table       | type | possible_keys             | key  | key_len | ref  | rows | Extra                       |
   +----+-------------+-------------+------+---------------------------+------+---------+------+------+-----------------------------+
   |  1 | SIMPLE      | wp_comments | ALL  | comment_approved_date_gmt | NULL | NULL    | NULL |  567 | Using where; Using filesort |
   +----+-------------+-------------+------+---------------------------+------+---------+------+------+-----------------------------+
   

   Another part of the issue is the ordering by comment_post_ID

   Consider this which will use an index:

   mysql> explain SELECT *, @post_id := comment_post_ID as cpID FROM wp_comments WHERE comment_approved = 1 ORDER BY comment_date_gmt DESC LIMIT 10;
   +----+-------------+-------------+-------+---------------------------+------------------+---------+------+------+-------------+
   | id | select_type | table       | type  | possible_keys             | key              | key_len | ref  | rows | Extra       |
   +----+-------------+-------------+-------+---------------------------+------------------+---------+------+------+-------------+
   |  1 | SIMPLE      | wp_comments | index | comment_approved_date_gmt | comment_date_gmt | 8       | NULL |   10 | Using where |
   +----+-------------+-------------+-------+---------------------------+------------------+---------+------+------+-------------+
   

   The latter query may affect your results but it will be much more efficient.

   The better solution may be some combination of MySQL query and PHP filtering to get the desired results.

   Since this is a plugin, you may consider opening this as an issue with the plugin developer since it is going to result in poor performance for any website with a large number of comments.

   This answer is by no means a solution, but hopefully points you in the right direction. The logic of the query and processing need to really be reconsidered when it comes to a site with lots of comments.

2. Without seeing actual data, and doing a somewhat educated guess, you are looking for the most recent “approved” posts, and then want to get the most recent 2 comments per post. The overall query was ok, I just took it one step further and give it a shot.

   SELECT 
         wpcoms.*
      FROM 
         ( SELECT 
                 *,
                 @num := if(@post_id = c1.comment_post_ID, @num + 1, 1) as row_number,
                 @post_id := c1.comment_post_ID as cpID
              FROM 
                 ( select distinct c2.comment_post_id
                      from wp_comments c2
                      where c2.comment_approved = 1
                      order by c2.comment_post_id desc
                      limit 6 ) Just6
                      JOIN wp_comments c1
                      ON Just6.comment_post_id = c1.comment_post_id
              WHERE 
                 c1.comment_approved = 1 
              ORDER BY 
                 c1.comment_post_ID DESC,
                 c1.comment_ID DESC
         ) as wpcoms
      WHERE 
         wpcoms.row_number <= 2
      ORDER BY 
         wpcoms.comment_date DESC
      LIMIT 6;
   

   The only real adjustment was to have an inner sub-query that pre-qualifies only comment post ID values that had an approved comment DISTINCT PER COMMENT_POST_ID and limiting that to only 6 records. By doing that, and joining back to the comments table on those qualified comment_post_id’s, you are not querying EVERY possible post, just the qualifying 6. From that, those comments are cut down to a maximum of just 2 per any comment post and then finalizing your list down to 6 total entries. However, if you always want 6 comment posts, get rid of the outer limit 6. This way, you would have at most 12 entries which would be 2 possible for the 6 inner-qualified comment post id values.

   Now, not knowing your indexes, I would suggest the following TWO compound indexes on your wp_comments table. But AT LEAST index #1

   1.  ( comment_approved, comment_post_id )
   2.  ( comment_post_id, comment_id )
   

Comments are closed.