bbPress Optimize -- Optimizes bbPress queries for large bbPress forums

bbp-optimize.php

<?php
/*
Plugin Name: bbPress Optimize
Plugin URI: https://www.scottkclark.com/
Description: Optimizes bbPress queries for large bbPress forums
Version: 1.1
Author: Scott Kingsley Clark
Author URI: https://www.scottkclark.com/
*/

global $bbp_optimize;
$bbp_optimize = [];

/**
 * Optimize bbPress queries.
 *
 * @param string $query Original query.
 *
 * @return string Optimized query.
 */
function bbp_optimize_queries( $query ) {
	global $wpdb, $bbp_optimize;

	if ( false !== strpos( $query, 'SELECT COUNT(ID) FROM ' ) && false !== strpos( $query, ' ) AND post_status = \'publish\' AND post_type = \'reply\'' ) ) {
		$query = 'SELECT "0" AS `the_count`';
	} elseif ( false !== strpos( $query, 'SELECT COUNT( DISTINCT post_author ) FROM ' ) && false !== strpos( $query, ' AND post_status = \'publish\' AND post_type = \'reply\' ) OR ( ID = ' ) && false !== strpos( $query, ' AND post_type = \'topic\' )' ) ) {
		preg_match( '/ AND post_status = \'publish\' AND post_type = \'reply\' \) OR \( ID = (\d+) AND post_type = \'topic\' \)/', $query, $topic );

		$count = (int) get_post_meta( $topic[1], '_bbp_voice_count', true );

		$user = get_current_user_id();

		if ( empty( $count ) ) {
			$count = 1;

			update_post_meta( $topic[1], '_bbp_voice_users', [ $user ] );
		} else {
			$users = (array) get_post_meta( $topic[1], '_bbp_voice_users', true );

			if ( ! in_array( $user, $users ) ) {
				$users[] = $user;

				$count ++;

				update_post_meta( $topic[1], '_bbp_voice_users', $users );
			}
		}

		$query = 'SELECT ' . $count . ' AS `the_count`';
	} elseif ( false !== strpos( $query, "{$wpdb->postmeta}.meta_key = '_bbp_last_active_time'" ) && false !== strpos( $query, "SELECT SQL_CALC_FOUND_ROWS  {$wpdb->posts}.ID" ) && false !== strpos( $query, "{$wpdb->posts}.post_type = 'topic'" ) ) {
		preg_match( '/ ' . $wpdb->posts . '.post_parent = (\d+)/', $query, $parent );

		$parent = (int) $parent[1];

		preg_match( '/LIMIT\s*\d+\,\s*\d+/', $query, $limit );

		$limit = $limit[0];

		if ( 0 < $parent ) {
			$query = "
				SELECT SQL_CALC_FOUND_ROWS p.ID
				FROM {$wpdb->posts} AS p
				WHERE p.post_parent = {$parent}
				AND p.post_type = 'topic'
				AND (p.post_status = 'publish'
				OR p.post_status = 'closed'
				OR p.post_status = 'private'
				OR p.post_status = 'hidden')
				ORDER BY COALESCE( (
				SELECT MAX( post_date )
				FROM {$wpdb->posts} r1
				WHERE r1.post_parent = p.id ) , p.post_date ) DESC
				{$limit}
			";
		}
	} elseif ( false !== strpos( $query, "SELECT ID FROM {$wpdb->posts}" ) && false !== strpos( $query, " AND post_status IN ( 'publish', 'closed' ) AND post_type = 'topic' ORDER BY ID DESC" ) ) {
		preg_match( '/ WHERE post_parent = (\d+) AND /', $query, $parent );

		$parent = (int) $parent[1];

		if ( 0 < $parent ) {
			$bbp_optimize['last_forum'] = $parent;

			$query = "
				SELECT /*bbpress optimized*/ ID
				FROM {$wpdb->posts}
				WHERE post_parent = %d
					AND post_status IN ( 'publish', 'closed' )
					AND post_type = 'topic'
				ORDER BY ID DESC
				LIMIT 1
			";

			$query = $wpdb->prepare( $query, $parent );
		}
	} elseif ( isset( $bbp_optimize['last_forum'] ) && false !== strpos( $query, "SELECT ID FROM {$wpdb->posts} WHERE post_parent IN ( " ) && false !== strpos( $query, " ) AND post_status = 'publish' AND post_type = 'reply' ORDER BY ID DESC LIMIT 1" ) ) {
		$forum_id = (int) $bbp_optimize['last_forum'];

		$query = "
			SELECT p.ID
			FROM {$wpdb->posts} AS p
			LEFT JOIN {$wpdb->postmeta} AS pm ON pm.post_id = p.ID
			WHERE pm.meta_key = '_bbp_forum_id' AND pm.meta_value = %s
				AND p.post_status IN ( 'publish', 'closed' )
				AND p.post_type = 'reply'
			ORDER BY p.ID DESC
			LIMIT 1
		";

		$query = $wpdb->prepare( $query, $forum_id );

		unset( $bbp_optimize['last_forum'] );
	}

	return $query;
}

add_filter( 'query', 'bbp_optimize_queries' );

/**
 * Optimize topic count queries.
 *
 * @param int $total_topics Total topics.
 * @param int $forum_id     Forum ID.
 *
 * @return int Topic count.
 */
function bbp_optimize_topic_count( $total_topics, $forum_id ) {
	global $wpdb;

	$query = "
		SELECT COUNT(*)
		FROM {$wpdb->posts}
		WHERE post_parent = %d
			AND post_status IN ( 'publish', 'closed' )
			AND post_type = 'topic'
		ORDER BY ID DESC
		LIMIT 1
	";

	$topics = $wpdb->get_var( $wpdb->prepare( $query, $forum_id ) );

	$total_topics += ( $topics - 1 );

	update_post_meta( $forum_id, '_bbp_topic_count', (int) $topics );
	update_post_meta( $forum_id, '_bbp_total_topic_count', (int) $total_topics );

	return $total_topics;
}

add_filter( 'bbp_update_forum_topic_count', 'bbp_optimize_topic_count', 10, 2 );

+1 on the question above!

@sc0ttkclark Does this works alright for you? I really need to use this as we upgrade the forums. Posting time is drastically slow right now as our forums are pretty huge.

This works great, we use it on https://pods.io/forums/ even though now we have closed the ability to add new topics recently. We've been using it for years.

I just updated this script with what we've been using since I originally posted this, it has a couple of minor tweaks but it's still much better.

Thank you so much, this is very helpful. It easily saved us 10-15s. And since we need the updated replies count for forums in back end, I've hooked on manage_forum_posts_custom_column to run the update function at intervals. I'm using the first two solutions only as of yet while I go through the rest of them to make sure it's safe for me.

Glad it's helpful for you! I hope bbPress continues to improve their performance.