[Plugin: WordPress Popular Posts] To the Admin of WPP – Slow Queries

Hi SantaCruze,

Ever since this plugin was published many people has reported similar issues and you’re the first one to actually share a slow query log (parcially, at least) with me, so thank you.

While reading it, a few ideas came to me on how to improve a couple of things that in the end should make things easier on the database. Will start testing this upcoming weekend and get back to you later.

Hi Hector — thanks for developing the plugin! We’ve been using it as part of our theme for the last two years and it’s been nothing but useful for our users! Unfortunately, it seems that our host has recently pulled down our database due to overloading the server.

According to them:

Problematic query:
SELECT wp_posts.ID, wp_posts.post_title , (SUM(wp_popularpostsdatacache.pageviews)) AS ‘pageviews’ FROM wp_posts RIGHT JOIN wp_popularpostsdatacache ON wp_posts.ID = wp_popularpostsdatacache.id WHERE wp_posts.post_status = ‘publish’ AND wp_posts.post_password = ” AND wp_popularpostsdatacache.day >= ‘2012-04-25’ – INTERVAL 30 DAY GROUP BY wp_posts.ID ORDER BY pageviews DESC LIMIT 7

EXPLAIN:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE wp_popularpostsdatacache ALL id 34841 Using where; Using temporary; Using filesort
1 SIMPLE wp_posts eq_ref PRIMARY PRIMARY 8 GMCweekly.wp_popularpostsdatacache.id 1 Using where

This query examines 34841 rows.

Since then, we’ve disabled the plugin so we could be put back up… but I was hoping there would be something you would recommend us do to work around the issue at hand.

Again, thanks for your hard work!

Alex

Hi Alex,

The query part has been completely rewritten and I’m currently testing the plugin. Hopefully, it should be less heavy on the database.

If you want to give it a try let me know and I’ll send you the beta version (well, it’s more like an alpha since there are some other things that need to be done but it works).

Héctor, we have a heavy traffic site suffering from the same issue here, can you send the beta version to test on our site ?

Hi Racer-X,

It’s still under development and some features have been disabled. If you don’t mind that, I’ll upload it and post the link here so you can try it out.

It′s not a problem. Send me the bytes ??

Hector, it appears the problem with the query is the INTERVAL function. I took Alex’s query and changed:

AND wp_popularpostsdatacache.day >= '2012-04-25' - INTERVAL 30 DAY

with

AND wp_popularpostsdatacache.day >= '2012-04-25'
 AND wp_popularpostsdatacache.day <= '2012-05-25'

The query time went from 1.5 – 3.0 seconds down to .009 – .1 seconds. I tested it about 20 times with the same results. I couldn’t find any concensus on the Internets that the INTERVAL function in MySQL is heavy, but it appears to be in this query. I looked through your plugin and this should be an easy fix using the ADDDATE() function.

Using the ADDDATE() function under testing, I was getting .05 seconds average query time.

$range = $table."cache.day >= '".gmdate("Y-m-d")."' AND ".$table."cache.day <= ADDDATE('".gmdate("Y-m-d")."', INTERVAL 30 DAY)";

Hi brokentwig,

That part of the query has been already changed on the development version. Actually I made some major changes to all queries and wrote them from scratch using a different approach, and now they’re using the DATE_SUB function instead. For example:

AND comment_date > DATE_SUB('".$this->now()."', INTERVAL 1 MONTH)

Will try your solution and use whichever gives the best performance.

Cool. Would you mind posting a link of your alpha version?

Alright, here it is! Please keep in mind that this is a development version and even though I did a full test of everything some bugs might show up.

There are some new, yet to be documented features in this release. You might want to give them a try and post feedbacks here.

I noticed Exclude Categories was quoted out. Is this still under development?

Nope. It’s still there, only that it works in a different way now.

If you’re using the widget, find it in Post Filter. Once enabled, set the category(ies) you want to exclude with a minus sign in front. For example: -25,-3,-15.

If you are using the template tag, this is how it is done:

wpp_get_mostpopular('order_by=views&cat=-25,-3,-15');

P.S.: same parameters apply for the shortcode version.

I couldn’t find the Post Filter in the widget. Maybe I’m blind.

So I tried the shortcode, and the category I excluded still appears in the list. Does data need to collect first?

Thanks for your help. Great plugin!

Sorry, my bad. It’s Post type (haven’t got used to it yet hehe). Maybe I should rename it to Post filter instead.

If the posts also belong to a non-excluded category these will still be included in the listing. If it’s not the case, please let me know.

I see a number of files in the download that look like they are no longer used. Can you give us a list of the files that are required to be uploaded for the plugin? Thanks.