stofa
Forum Replies Created
-
Forum: Plugins
In reply to: [WP Popular Posts] Query performanceHi Hector,
The query suggested by Stack Overflow folks is valid. To avoid some warnings, this filter in the WHERE condition : c.comment_approved = 1 should by changed in c.comment_approved = ‘1’ as the column comment_approved in wp_comments is declared as varchar.
To include the comments in my original query, just add another subquery as follows :
SELECT id, post_title AS title, post_author AS uid, v.pageviews, c.comment_count from wp_posts p, (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, (SELECT COUNT(comment_post_ID) 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 WHERE 1 = 1 AND p.post_type IN(‘post’) AND p.post_password = ” AND p.post_status = ‘publish’ and v.postid=p.id and p.id=c.comment_post_ID ORDER BY pageviews DESC LIMIT 5 OFFSET 0;
Testing the two versions in my environment, it seems that my version is a little bit faster. But the first version ( Stack Overflow folks) is acceptable.
Stofa.
Forum: Plugins
In reply to: [WP Popular Posts] Query performanceHi Hector,
I think you shoukld disable query cache by putting SQL_NO_CACHE just after SELECT in the query.
Believe me, i re-run the test on 5.5 and 5.6 Percona mysql server.
This time, i did the test on a separate instances on a separate server used only for test purpose.The times are as i stated at the begin.
5 seconds for the original query
900 ms for the modified one.It’s up to you take the final decision but believe me with the new version plugin users will be very happy.
Thanks.
Stofa
Forum: Plugins
In reply to: [WP Popular Posts] Query performanceHi Hector,
Basically it is not a real subquery but a join between a table and inline query.
I agree with you about the bad performance of a subquery against a join but this is true if for instance use a query like this :select …. from table1 where col1 is in ( select col2 from table2 );
The two versions of the query executed on my live instance gave me the following response time :
Current version :5 seconds
Modified version : 900 ms.
As the query takes a lot, many of our customers are disintalling the plugin in order to make wordpress working.
Thanks
Stofa.
Forum: Plugins
In reply to: [WP Popular Posts] Query performanceHi Hector,
Let me reopen this case to suggest a rewrite of the query :
This is the current version :
SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, SUM(v.pageviews) AS pageviews FROM
wp_popularpostssummaryv LEFT JOINwp_postsp ON v.postid = p.ID WHERE 1 = 1 AND p.post_type IN(‘post’) AND v.view_datetime > DATE_SUB(‘2018-08-28 10:00:04’, INTERVAL 29 DAY) AND p.post_password = ” AND p.post_status = ‘publish’ GROUP BY v.postid ORDER BY pageviews DESC LIMIT 5 OFFSET 0;We can improve it by using a subquery in this mode :
select p.ID AS id, p.post_title AS title, p.post_author AS uid, v.pageviews from wp_posts p,(select sum(pageviews) pageviews,postid from wp_popularpostssummary where view_datetime > DATE_SUB( ‘2018-08-28 10:00:04’, INTERVAL 29 DAY) group by postid) v where v.postid=p.ID AND p.post_type IN(‘post’) and p.post_password = ” AND p.post_status = ‘publish’ order by v.pageviews desc limit 5;
Moreover, is it possible to remove the time part in the WHERE condition and use only the date part ?
I mean, something like this :
DATE_SUB( ‘2018-08-28 00:00:00’, INTERVAL 29 DAY)In this way, if the query cache is enabled on mysql server side, the result is cached and the query will be very fast.
By using the condition whith the time part, the query is always different and then the cache is never used.
Thanks
Stofa.