Optimization of slow request in WordPress plugin "Best WordPress Comments Recent Comments"

Question

I optimize MySQL queries and my slow query log shows me that the WordPress plugin “Better WordPress Recent Comments”, which shows the last 5 last comments grouped by posts, uses 1.26 seconds for a db request, which is a long time for web site — especially when the next website is just a few steps away.

Here is a 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;

288.634, 96 000 . , , , , , . .

Answer 1

, , - , SELECT *, @num..., , , MySQL comment_approved.

, , MySQL, , .

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 |
+----+-------------+-------------+------+---------------------------+------+---------+------+------+-----------------------------+

- comment_post_ID

, :

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 |
+----+-------------+-------------+-------+---------------------------+------------------+---------+------+------+-------------+

, .

MySQL PHP .

, , .

, , , . , .

Answer 2

, "" , 2 . , .

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;

, , , DISTINCT PER COMMENT_POST_ID 6 . ___, , 6. , 6 . , 6 , 6. , 12 , 2 6 .

, , wp_comments. AT LEAST # 1

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