As stated here, the following query rewrite performs noticeable better than the current version:
SELECT ID AS id, post_title AS title, post_author AS uid, v.pageviews, IFNULL(c.comment_count, 0) AS comment_count
FROM `wp_posts` p
INNER JOIN (SELECT SUM(pageviews) pageviews, postid FROM `wp_popularpostssummary` v WHERE v.view_datetime > DATE_SUB('2018-08-28 10:00:04', INTERVAL 59 DAY) GROUP BY postid) v ON p.ID = v.postid
LEFT JOIN (SELECT COUNT(comment_post_ID) AS comment_count, comment_post_ID FROM `wp_comments` c WHERE c.comment_date_gmt > DATE_SUB('2018-08-28 10:00:04', INTERVAL 59 DAY) AND c.comment_approved = '1' GROUP BY comment_post_ID) c ON p.ID = c.comment_post_ID
WHERE 1 = 1 AND p.post_type IN('post') AND p.post_password = '' AND p.post_status = 'publish' ORDER BY pageviews DESC LIMIT 5 OFFSET 0;
Note that c.comment_approved = '1' uses a string instead of an integer as said column has been declared as a varchar in the database.
This of course will require reviewing every query variation built by the WPP_Query class which might be a breaking change for people hooking into it to customize the resulting query.
As stated here, the following query rewrite performs noticeable better than the current version:
Note that
c.comment_approved = '1'
uses a string instead of an integer as said column has been declared as a varchar in the database.This of course will require reviewing every query variation built by the
WPP_Query
class which might be a breaking change for people hooking into it to customize the resulting query.