EverythingsAMatterOfTime

I cannot see any monthly cron tasks but I have been able to locate the slow query that is responsible:

class-post-type-sitemap-provider.php

# Query_time: 164.193066  Lock_time: 0.000181 Rows_sent: 100  Rows_examined: 253924
SET timestamp=1554073483;
SELECT l.ID, post_title, post_content, post_name, post_parent, post_author, post_modified_gmt, post_date, post_date_gmt
			FROM (
				SELECT wp_posts.ID
				FROM wp_posts
				
				
		
		WHERE wp_posts.post_status = 'publish'
			AND wp_posts.post_type = 'post'
			AND wp_posts.post_password = ''
			AND wp_posts.post_date != '0000-00-00 00:00:00'
		
					
				ORDER BY wp_posts.post_modified ASC LIMIT 100 OFFSET 66800
			)
			o JOIN wp_posts l ON l.ID = o.ID;

As you can see the query time is very long!

on my system there seems to be a daily process (which places some demand, but not too bad). However there is about 2 days a month (last day of month, first day of next month and/or the day after) where it stalls the server to a complete stop for about 2 hours.

There must be a monthly process being run?

Were the focus keywords able to be optimized?

I’m still running Yoast SEO thou the slow query bottleneck is staring to cause some CPU issues with our medium/large site.

Thanks for your persistence Hector!

we find out that code in update range summary executes a query which adds value to the last_viewed attribute.

 $result2 = $wpdb->query( $wpdb->prepare(
            "INSERT INTO {$table}summary
            (postid, pageviews, view_date, last_viewed) VALUES (%d, %d, %s, %s)
            ON DUPLICATE KEY UPDATE pageviews = pageviews + %d, last_viewed = %s;",
            $post_ID,
            $views,
            $curdate,
            $now,
            $views,
            $now
        ));

However, in the database, the “wp_popularpostssummary” table does not have this attribute, but the “view_datatime” attribute. So I think this result in this SQL query could not be correctly executed and such function return False.

Database Picture

• This reply was modified 7 years, 4 months ago by EverythingsAMatterOfTime.

??

I took a deeper look and couldn’t reconcile the ID issue – not sure if I stressed this fact earlier but both sites use the same database and therefore the same post IDs.

WPP is updating the views count of the post IDS that WordPress returns. For some reason, WordPress is returning the IDs of the test site.

There’s only one post ID for the same post in both sites. Clearly WordPress is only returning incorrect values to the plugin. Would you have any idea where I can take a closer look at this?

Thanks Hector

Do you know where this URL is kept in the wordpress database? I want to take a look at it.

Hi Hector!

I had another look after reading your post –

We have two WordPress (test & live) connected to the same database. Sometimes we’ll make some changes on the test server and then deploy that version to the live server. As they’re literally connected to the same database, I think some URLs in the database was accidentally changed to the URL of the test site, so now the WPP of the live site only count the view counts of the test site.

Basically, we only post on the live site, so the IDs are the same on the live site and the test site. I think it’s something wrong with the WP database, just wonder if you know which part we might have changed?

Cheers.

I am getting this also. Also contacted Nextscripts admin on a paid version but they have not replied for over 2 weeks – very lousy indeed!

(very promising plugin but the response from admin is a big letdown – I wouldn’t recommend anyone pay them unless their attitude changes!!!)

Hi Hector!

This is what we expect (the correct behaviour on the home page): the middle tab indicates ranking by views and the right tab is ranking by comment counts.

https://s3-ap-southeast-2.amazonaws.com/s3-1688-wp-media/temp/1.png

=> https://s3-ap-southeast-2.amazonaws.com/s3-1688-wp-media/temp/2.png

This is what we got on the post page: the middle tab and the right tab are the same.

https://s3-ap-southeast-2.amazonaws.com/s3-1688-wp-media/temp/3.png

=> https://s3-ap-southeast-2.amazonaws.com/s3-1688-wp-media/temp/4.png

Using the exact same code. （WordPress v4.75 – WordPress Popular Posts 3.3.4）

Let me know if anything else I need to clarify.

Cheers!

apologies Hector – code is below:

<div id="popular-news">
    <ul class="nav nav-pills">
        <li class="active">
            <a data-toggle="tab" href="#weekly">
                <h3 class="popular-news-title">本周排行</h3>
            </a>
        </li>
        <li>
            <a data-toggle="tab" href="#daily">
                <h3 class="popular-news-title">每日排行</h3>
            </a>
        </li>
        <li>
            <a data-toggle="tab" href="#comments">
                <h3 class="popular-news-title">评论排行</h3>
            </a>
        </li>
    </ul>
    <div class="tab-content">
        <div id="weekly" class="tab-pane in active">
<?php
$args = array(
    'header_start' => '<h3 id="popular-news-title">',
    'header_end'   => '</h3>',
    'limit'        => 10,
    'range'        => 'weekly',
    'freshness'    => 1,
    'order_by'     => 'views',
    'post_type'    => 'post',
    'wpp_start'    => '<ol id="popular-news-list">',
    'wpp_end'      => '</ol>',
    'post_html'    => '<li><a target="_blank" title="{text_title}" href="{url}">{text_title}</a><span style="visibility: hidden;" class="popular-news-view-count">{views}</span></li>'
);
wpp_get_mostpopular($args);
?>
        </div>
        
        <div id="daily" class="tab-pane">
<?php
$args = array(
    'header_start' => '<h3 id="popular-news-title">',
    'header_end'   => '</h3>',
    'limit'        => 10,
    'range'        => 'daily',
    'freshness'    => 1,
    'order_by'     => 'views',
    'post_type'    => 'post',
    'wpp_start'    => '<ol id="popular-news-list">',
    'wpp_end'      => '</ol>',
    'post_html'    => '<li><a target="_blank" title="{text_title}" href="{url}">{text_title}</a><span style="visibility: hidden;" class="popular-news-view-count">{views}</span></li>'
);
wpp_get_mostpopular($args);
?>
        </div>
        <div id="comments" class="tab-pane">
<?php
$args = array(
    'header_start' => '<h3 id="popular-news-title">',
    'header_end'   => '</h3>',
    'limit'        => 10,
    'range'        => 'weekly',
    'freshness'    => 1,
    'order_by'     => 'comments',
    'post_type'    => 'post',
    'wpp_start'    => '<ol id="popular-news-list">',
    'wpp_end'      => '</ol>',
    'post_html'    => '<li><a target="_blank" title="{text_title}" href="{url}">{text_title}</a><span>{comments}</span></li>'
);
wpp_get_mostpopular($args);
?>
        </div>
    </div>
</div>

Hey Hector!

This is the code we use. It works well on other pages including the home page. Only on the post pages, the comments list will show as the same as the daily list.

[Large code excerpt removed by moderator per forum rules. Please use Pastebin or a Gist for all large code excerpts, they work better anyway.]

It won’t display the top comments posts (best guess is that it is unable to fetch the relevant data or perhaps have trouble displaying it).

• This reply was modified 7 years, 8 months ago by James Huff.