bbp_update_forum_reply_count on a 1.8MM post bbPress forum takes ~6-10 seconds

[vibol]

This can likely use some optimization.

I haven't dug in enough to come up with a solution but I believe this function is what causes the massive query attached. Perhaps these aggregates should be placed in a separate table and incremented or decremented according to the posts added/removed. At the numbers I'm working with, eventual consistency of the aggregate numbers is acceptable.

Scenarios affected: Add new post, Reply to post, Trash post, Delete post.

Action execution times: ~6-10s depending on freshness of data in memory and size of forum.

System: 12-core, 64GB RAM

Function in question: bbp_update_forum_reply_count

Actual query: see attached.

[johnjamesjacoby]

Moving to 2.5 so we can address this with a few other performance issues. Thanks for attaching the log.

[johnjamesjacoby]

Bump to 2.6.

[netweb]

Some quick stats based on 1 forum with 827 Topics and 1,640 replies ( @vibol's above info is ~22,500 topics in 1 forum)

bbp_update_forum_reply_count (​\includes\forums\functions.php#1519)

Current: Query took 0.0327 sec (Each of the 827 topic id's in this forum omitted for readability)

SELECT COUNT(ID) 
FROM wp_posts 
WHERE post_parent 
IN ( 103020,103016,103013,103010,103006... ) 
AND post_status = 'publish' AND post_type = 'reply';

Alternate: Query took 0.0141 sec

<?php
$reply_count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_parent IN ( SELECT ID  FROM `wp_posts` WHERE `post_parent` = $forum_id   ) AND post_status = '%s' AND post_type = '%s';", bbp_get_public_status_id(), bbp_get_reply_post_type() ) );

SQL

SELECT COUNT(ID)
FROM wp_posts 
WHERE post_parent 
IN ( 
    SELECT ID  FROM `wp_posts` WHERE `post_parent` = 96480  
) 
AND post_status = 'publish' AND post_type = 'reply'

This conservatively halves the query time though because we are not using bbp_forum_query_topic_ids we are including 'any' reply including those that are NOT public.

Pretty much just thinking out loud here...

[netweb]

Related: #2533

[netweb]

Extending this ticket to include 'all the forum things' around bbp_update_forum()

• bbp_update_forum_last_topic_id
• bbp_update_forum_last_reply_id
• bbp_update_forum_last_active_id
• bbp_update_forum_last_active_time
• bbp_update_forum_subforum_count
• bbp_update_forum_reply_count
• bbp_update_forum_topic_count
• bbp_update_forum_topic_count_hidden

[netweb]

Closing this ticket in favor of #1799 as it already has a work in progress patch for bbp_update_forum_reply_count