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

i'm optimizing queries against mysql , slow queries log shows me wordpress plugin "better wordpress recent comments" widget shows last 5 recent comments grouped posts, uses 1.26 seconds query db long time website - when next website click away.

here slow query:

query_time: 1.265625 lock_time: 0.000000 rows_sent: 6 rows_examined: 288634

set timestamp=1443741678;

select wpcoms.* ( select *, @num := if(@post_id = comment_post_id, @num + 1, 1) row_number, @post_id := comment_post_id cpid wp_comments comment_approved = 1 order comment_post_id desc, comment_id desc ) wpcoms wpcoms.row_number <= 2 order wpcoms.comment_date desc limit 6; 

rows examined says 288.634, database consists of 96.000 comments. surely should possible improve few comments examined in short time instead, there few recent posted comments shows. thanks.

one of main problems see inner query, select *, @num... because results in full table scan cause mysql not use comment_approved index.

the reason because if @ query, telling mysql select every row in table ordered post id no limit.

mysql> explain select *, @post_id := comment_post_id cpid wp_comments comment_approved = 1 order comment_post_id desc, comment_id desc limit 10; +----+-------------+-------------+------+---------------------------+------+---------+------+------+-----------------------------+ | id | select_type | table       | type | possible_keys             | key  | key_len | ref  | rows |                       | +----+-------------+-------------+------+---------------------------+------+---------+------+------+-----------------------------+ |  1 | simple      | wp_comments |  | comment_approved_date_gmt | null | null    | null |  567 | using where; using filesort | +----+-------------+-------------+------+---------------------------+------+---------+------+------+-----------------------------+ 

another part of issue ordering comment_post_id

consider use index:

mysql> explain select *, @post_id := comment_post_id cpid wp_comments comment_approved = 1 order comment_date_gmt desc limit 10; +----+-------------+-------------+-------+---------------------------+------------------+---------+------+------+-------------+ | id | select_type | table       | type  | possible_keys             | key              | key_len | ref  | rows |       | +----+-------------+-------------+-------+---------------------------+------------------+---------+------+------+-------------+ |  1 | simple      | wp_comments | index | comment_approved_date_gmt | comment_date_gmt | 8       | null |   10 | using | +----+-------------+-------------+-------+---------------------------+------------------+---------+------+------+-------------+ 

the latter query may affect results more efficient.

the better solution may combination of mysql query , php filtering desired results.

since plugin, may consider opening issue plugin developer since going result in poor performance website large number of comments.

this answer no means solution, points in right direction. logic of query , processing need reconsidered when comes site lots of comments.