Inefficient database queries

Jeremy Pry
(@jpry)

10 years ago

This is similar to this issue.

There are two inefficient queries that are run by this plugin every night at midnight. You can see these queries in the wpp_cache_maintenance() function:

function wpp_cache_maintenance() {
global $wpdb;

// delete posts that have not been seen in the past 30 days
$wpdb->query( “DELETE FROM “.$wpdb->prefix.”popularpostsdatacache WHERE day < DATE_SUB(‘”.$this->curdate().”‘, INTERVAL 30 DAY);” );

// delete posts that have been deleted or trashed – added on ver 2.3.3
$wpdb->query( “DELETE FROM {$wpdb->prefix}popularpostsdata WHERE postid IN (SELECT c.id FROM (SELECT id FROM {$wpdb->prefix}popularpostsdatacache GROUP BY id) c LEFT JOIN {$wpdb->posts} p ON c.id = p.ID WHERE p.ID IS NULL OR p.post_status = ‘trash’);” );
$wpdb->query( “DELETE FROM {$wpdb->prefix}popularpostsdatacache WHERE id IN (SELECT c.id FROM (SELECT id FROM {$wpdb->prefix}popularpostsdatacache GROUP BY id) c LEFT JOIN {$wpdb->posts} p ON c.id = p.ID WHERE p.ID IS NULL OR p.post_status = ‘trash’);” );

}

The queries are found after the //delete posts that have been deleted or trashed section. The first query took over 22 seconds to modify zero rows, and the second query took over 475 seconds to modify zero rows! That’s only 5 seconds short of 8 minutes!

These inefficient queries can cause a site to go down when the cron job is run, and should be made to run much more quickly.

https://wordpress.org/plugins/wordpress-popular-posts/

Viewing 1 replies (of 1 total)

Plugin Author

(@hcabrera)

Hi Jeremy,

Thanks for bringing this into my attention. Just made some changes to that piece of code on the development version and this should be far better (see purge_data()).

Warning: these changes above are not compatible with any of the official stable releases of the plugin (including latest 2.3.7 at the time of writing). Do not copy&paste the code into the current version of the plugin because it won’t work and will cause issues/errors.

If you’re feeling adventurous, here’s a hotfix that should be OK to use with the current release (2.3.7):

1. Open wordpress-popular-posts.php using an editor such as Dreamweaver / Notepad++ (or from your wp-admin, go to Plugins > Editor and select WordPress Popular Posts), and around line 1898 you’ll find the following piece of code:

function wpp_cache_maintenance() {
	global $wpdb;

	// delete posts that have not been seen in the past 30 days
	$wpdb->query( "DELETE FROM ".$wpdb->prefix."popularpostsdatacache WHERE day < DATE_SUB('".$this->curdate()."', INTERVAL 30 DAY);" );

	// delete posts that have been deleted or trashed - added on ver 2.3.3
	$wpdb->query( "DELETE FROM {$wpdb->prefix}popularpostsdata WHERE postid IN (SELECT c.id FROM (SELECT id FROM {$wpdb->prefix}popularpostsdatacache GROUP BY id) c LEFT JOIN {$wpdb->posts} p ON c.id = p.ID WHERE p.ID IS NULL OR p.post_status = 'trash');" );
	$wpdb->query( "DELETE FROM {$wpdb->prefix}popularpostsdatacache WHERE id IN (SELECT c.id FROM (SELECT id FROM {$wpdb->prefix}popularpostsdatacache GROUP BY id) c LEFT JOIN {$wpdb->posts} p ON c.id = p.ID WHERE p.ID IS NULL OR p.post_status = 'trash');" );

}

2. Replace that entire block with the following:

function wpp_cache_maintenance() {
	global $wpdb;

	// delete posts that have not been seen in the past 30 days
	$wpdb->query( "DELETE FROM ".$wpdb->prefix."popularpostsdatacache WHERE day < DATE_SUB('".$this->curdate()."', INTERVAL 30 DAY);" );

	// delete posts that have been deleted
	if ( $missing = $wpdb->get_results( "SELECT v.postid AS id FROM {$wpdb->prefix}popularpostsdata v WHERE NOT EXISTS (SELECT p.ID FROM {$wpdb->posts} p WHERE v.postid = p.ID);" ) ) {
		$to_be_deleted = '';

		foreach ( $missing as $deleted )
			$to_be_deleted .= $deleted->id . ",";

		$to_be_deleted = rtrim( $to_be_deleted, "," );

		$wpdb->query( "DELETE FROM {$wpdb->prefix}popularpostsdata WHERE postid IN({$to_be_deleted});" );
		$wpdb->query( "DELETE FROM {$wpdb->prefix}popularpostsdatacache WHERE id IN({$to_be_deleted});" );
	}

}

3. Save changes.

I have not tested this code but it should work. If it doesn’t, simply redownload the plugin to restore the original code.

Viewing 1 replies (of 1 total)

The topic ‘Inefficient database queries’ is closed to new replies.