Optimize slow query in WordPress plugin "Better WordPress Recent Comments"

I'm optimizing the queries against MySQL and my slow queries log shows me that the WordPress plugin "Better WordPress Recent Comments" Widget that shows the last 5 recent comments grouped by posts, uses 1.26 seconds to query the db which is a long time for a website - especially when the next website is just a click away.

Here is the slow query:

Query_time: 1.265625 Lock_time: 0.000000 Rows_sent: 6 Rows_examined: 288634

SET timestamp=1443741678;

SELECT wpcoms.*
FROM (
SELECT *,
@num := if(@post_id = comment_post_ID, @num + 1, 1) as row_number,
@post_id := comment_post_ID as cpID
FROM wp_comments
WHERE comment_approved = 1 ORDER BY comment_post_ID DESC,
comment_ID DESC
) as wpcoms
WHERE wpcoms.row_number <= 2
ORDER BY wpcoms.comment_date DESC
LIMIT 6;

Rows examined says 288.634, but my database consists of only 96.000 comments. That surely should be possible to improve so that just a few comments actually is examined in a very short time instead, as there is only the few recent posted comments it shows. Thanks.

Answers

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 )


Posted on by DRapp

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.

Posted on by drew010