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 i...