bbPress.org

Deleting a single reply takes 30–45 s on a large forum (unindexable meta_value lookups)

Summary

On a board with ~573,000 replies, permanently deleting one reply from
wp-admin/edit.php?post_status=trash&post_type=reply takes 42 seconds, and the web server returns a 500 before the request finishes:

[fastcgi:error] FastCGI: comm with server ".../php8.3.0.fcgi" aborted: idle timeout (30 sec),
    referer: .../wp-admin/edit.php?post_status=trash&post_type=reply
[fastcgi:error] FastCGI: incomplete headers (0 bytes) received from server ".../php8.3.0.fcgi"

The delete itself does go through, so the admin is left with an error screen and no way to tell
whether it worked.

Two separate queries are responsible. Both filter wp_postmeta.meta_value, which has
no index in the WordPress schema, so both degrade linearly with the size of the board.

Environment

• bbPress 2.6.20
• WordPress 7.1.3
• PHP 8.3
• MySQL 5.7.39 (InnoDB)
• Content: 47 forums (1 hidden), 1,391 topics, 572,724 published replies
• wp_postmeta: 2,395,731 rows; 574,165 of them _bbp_forum_id, 574,117 _bbp_topic_id
• Object cache: none (database-backed transients only)

How to reproduce

1. Import or generate a board with a few hundred thousand replies.
2. Trash a reply, then delete it permanently from the trash list.
3. Watch the request time, or profile with SAVEQUERIES.

Measured with SAVEQUERIES around a bare wp_delete_post( $reply_id, true )
on the CLI, with no HTTP layer involved: 42.35 s, 183 queries.

Cause 1 — bbp_get_child_counts() scans every row of a meta key

In includes/common/functions.php, bbp_get_child_counts() (since 2.6.0,
r6827) runs:

SELECT p.post_type AS type, p.post_status AS status, COUNT( * ) AS count
FROM wp_posts AS p
  LEFT JOIN wp_postmeta AS pm
    ON p.ID = pm.post_id AND pm.meta_key = '_bbp_forum_id'
WHERE pm.meta_value = '154459'
GROUP BY p.post_status, p.post_type

Because the WHERE clause rejects NULLs, MySQL turns the LEFT JOIN into
an inner join and drives from wp_postmeta. The only usable index is
meta_key, so it walks every row of that key and checks
meta_value row by row:

EXPLAIN (without an extra index)
  table: pm   type: ref   key: meta_key   key_len: 767   ref: const
  rows: 1127050   filtered: 10.00   Extra: Using where; Using temporary; Using filesort

Deleting one reply calls this five times — once for the topic and once for each of the four
ancestor forums — at 3.2–4.9 s each:

_bbp_topic_id:155815    3.88 s
_bbp_forum_id:154457    3.63 s
_bbp_forum_id:154458    4.56 s
_bbp_forum_id:154459    4.86 s
_bbp_forum_id:154460    3.18 s

The cache inside the function does not help: its key contains
wp_cache_get_last_changed( 'bbpress_posts' ), and the delete keeps modifying posts, so
every call after the first invalidation is a fresh query.

Cause 2 — the hidden-forum exclusion adds a CAST() that no index can serve

bbp_forum_query_last_reply_id() builds a deliberately lean WP_Query
(suppress_filters => true, no meta query). But pre_get_posts still runs,
and bbp_pre_get_posts_normalize_forum_visibility() injects
bbp_exclude_forum_ids( 'meta_query' ) because the board has one hidden forum. The
result:

SELECT wp_posts.ID FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE wp_posts.post_parent IN ( ...32 topic ids... )
  AND ( wp_postmeta.meta_key = '_bbp_forum_id'
        AND CAST( wp_postmeta.meta_value AS SIGNED ) NOT IN ('154499') )
  AND wp_posts.post_type = 'reply' AND wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC, wp_posts.ID DESC LIMIT 0, 1

The meta query is built with 'type' => 'NUMERIC' and
'compare' => 'NOT IN' (includes/forums/functions.php, around line 2806).
CAST( meta_value AS SIGNED ) makes the predicate non-sargable, so no index on
meta_value can ever be used, and NOT IN on a single hidden forum forces a
full join against the 574k _bbp_forum_id rows. Four of these run per delete,
0.7–2.8 s each.

Note the shape of the exclusion: it asks “is this reply not in the one
hidden forum”, which has to touch every reply, instead of “is this reply in one of the 46
visible forums” or, simpler, instead of filtering by the forum ids the caller already knows.

Workaround we are using

A composite index on wp_postmeta:

ALTER TABLE wp_postmeta ADD INDEX tq_key_value ( meta_key(32), meta_value(32) );

EXPLAIN (with the index)
  table: pm   type: ref   key: tq_key_value   key_len: 262   ref: const,const
  rows: 966   filtered: 100.00

Estimated rows for cause 1 drop from 1,127,050 to 966, the five child-count queries go from ~20 s
to 0.40 s in total, and the whole delete goes from 42.35 s to about
5 s — under the web server timeout, so the 500 is gone.

The index does nothing for cause 2: the CAST() still blocks it. Those queries are
what the remaining ~4.5 s is made of.

Suggested fixes

1. bbp_get_child_counts() should not filter on meta_value.
   The same numbers are available from indexed columns: replies of a topic are
   post_parent = <topic>, and topics of a forum are
   post_parent = <forum>, so a forum’s reply count is one join over
   post_parent (which is indexed) instead of a meta scan. If the meta lookup has
   to stay for sub-forum aggregation, bbPress could create its own index on
   (meta_key, meta_value) at activation, the way it already creates its other schema.
2. Drop the CAST() from bbp_exclude_forum_ids( 'meta_query' ).
   With 'type' => 'NUMERIC' removed — meta ids are stored as plain digit strings
   — the predicate becomes sargable and an index can serve it.
3. Prefer the positive form of the visibility filter.
   meta_value IN (<visible forum ids>) is selective;
   NOT IN (<hidden forum ids>) matches almost every row on the board.
4. Respect suppress_filters, or add an opt-out for the maintenance path.
   The recount queries run on ids bbPress already trusts; putting them through the public visibility
   normalisation is both slow and pointless.

Happy to test a patch against this dataset.