Title: Inefficient database queries
Last modified: August 21, 2016

---

# Inefficient database queries

 *  [Jeremy Pry](https://wordpress.org/support/users/jpry/)
 * (@jpry)
 * [12 years, 1 month ago](https://wordpress.org/support/topic/inefficient-database-queries/)
 * This is similar to [this issue](http://wordpress.org/support/topic/popular-posts-crashing-mysql?replies=4).
 * 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/](https://wordpress.org/plugins/wordpress-popular-posts/)

Viewing 1 replies (of 1 total)

 *  Plugin Author [Hector Cabrera](https://wordpress.org/support/users/hcabrera/)
 * (@hcabrera)
 * [12 years, 1 month ago](https://wordpress.org/support/topic/inefficient-database-queries/#post-4838798)
 * Hi Jeremy,
 * Thanks for bringing this into my attention. Just made some changes to that piece
   of code on the development version and [this](https://github.com/cabrerahector/wordpress-popular-posts/commit/ab3b2a3c68c21afb7d3e80fe6caba7c9096f44c9)
   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.

 * ![](https://ps.w.org/wordpress-popular-posts/assets/icon-256x256.png?rev=1232659)
 * [WP Popular Posts](https://wordpress.org/plugins/wordpress-popular-posts/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/wordpress-popular-posts/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/wordpress-popular-posts/)
 * [Active Topics](https://wordpress.org/support/plugin/wordpress-popular-posts/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/wordpress-popular-posts/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/wordpress-popular-posts/reviews/)

 * 1 reply
 * 2 participants
 * Last reply from: [Hector Cabrera](https://wordpress.org/support/users/hcabrera/)
 * Last activity: [12 years, 1 month ago](https://wordpress.org/support/topic/inefficient-database-queries/#post-4838798)
 * Status: not resolved