• This topic contains 1 voice and has 0 replies.
Viewing 1 post (of 1 total)
  • Author
    Posts
  • #34270

    Tproger
    Participant

    I suggest some modifications in counter.php file to improve database performance. Instead of doing many single inserts we can do one (or few) bulk inserts in one request.

    Just add two functions:

    /**
    	 * Prepare bulk insert or update views count.
    	 * 
    	 * @param int $id
    	 * @param string $type
    	 * @param string $period
    	 * @param int $count
    	 * @return void
    	 */
    	private function db_prepare_insert( $id, $type, $period, $count = 1 ) {
    		$count = (int) $count;
    
    		if ( ! $count ) {
    			$count = 1;
    		}
    
    		if ( ! empty( $this->values_to_insert ) ) {
    			$this->values_to_insert .= ', ';
    		}
    
    		$this->values_to_insert .= sprintf( '(%d, %d, "%s", %d)', $id, $type, $period, $count );
    
    		if ( strlen($this->values_to_insert) > self::MAX_INSERT_STRING_LENGTH ) {
    			$this->db_commit_insert();
    		}
    	}
    
    	/**
    	 * Actually inserts accumulated values to db.
    	 *
    	 * @global object $wpdb
    	 * @return bool
    	 */
    	private function db_commit_insert() {
    		if ( empty( $this->values_to_insert ) ) {
    			return false;
    		}			
    
    		global $wpdb;
    
    		$result = $wpdb->query("
    			INSERT INTO " . $wpdb->prefix . "post_views (id, type, period, count)
    			VALUES " . $this->values_to_insert . "
    			ON DUPLICATE KEY UPDATE count = count + VALUES(count)"
    		);
    
    		$this->values_to_insert = '';
    
    		return $result;
    	}

    And then use it in flush_cache_to_db:

    	/**
    	 * Flush views data stored in the persistent object cache into
    	 * our custom table and clear the object cache keys when done.
    	 * 
    	 * @global object $wpdb
    	 * @return bool
    	 */
    	public function flush_cache_to_db() {
    		global $wpdb;
    
    		$key_names = wp_cache_get( self::NAME_ALLKEYS, self::GROUP );
    
    		if ( ! $key_names ) {
    			$key_names = array();
    		} else {
    			// create an array out of a string that's stored in the cache
    			$key_names = explode( '|', $key_names );
    		}
    
    		foreach ( $key_names as $key_name ) {
    			// get values stored within the key name itself
    			list( $id, $type, $period ) = explode( self::CACHE_KEY_SEPARATOR, $key_name );
    			// get the cached count value
    			$count = wp_cache_get( $key_name, self::GROUP );
    
    			// store cached value in the db
    			$this->db_prepare_insert( $id, $type, $period, $count );
    
    			// clear the cache key we just flushed
    			wp_cache_delete( $key_name, self::GROUP );
    		}		
    
    		// actually flush values to db (if any left)
    		$this->db_commit_insert();
    
    		// remember last flush to db time
    		wp_cache_set( 'last-flush', time(), self::GROUP );
    
    		// delete the key holding the list itself after we've successfully flushed it
    		if ( ! empty( $key_names ) ) {
    			wp_cache_delete( self::NAME_ALLKEYS, self::GROUP );			
    		}
    
    		return true;
    	}

    Also, I added timestamp of last database flush for my internal purposes. I suggest adding some hooks there so I can do it in my code. Something like do_action( 'pvc_after_flush_cache_to_db' );

    And we need some additional local variables:

    const MAX_INSERT_STRING_LENGTH = 25000;
    private $values_to_insert = '';

    I implemented this tweak on my website and it works as expected with less DB queries. What do you think?

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.