[Tommy Kolkman]

Hello,

We’ve got a really big bbPress forum running at http://realmadridcf.nl.

At some point though, the site crashed because of heavy query. After some inspection of the SQL slow query log, we found out that it was this query:

# Query_time: 2.798700 Lock_time: 0.000138 Rows_sent: 5 Rows_examined:

366600

SET timestamp=1321347478;

SELECT SQL_CALC_FOUND_ROWS wpecs_posts.* FROM wpecs_posts INNER JOIN

wpecs_postmeta ON (wpecs_posts.ID = wpecs_postmeta.post_id) WHERE 1=1

AND wpecs_posts.post_type IN ('topic', 'reply') AND

(wpecs_posts.post_status = 'publish' OR wpecs_posts.post_status =

'closed') AND ( (wpecs_postmeta.meta_key = '_bbp_forum_id' AND

CAST(wpecs_postmeta.meta_value AS CHAR) NOT IN

('8365804','8558913','8558914','8561009','8561010','187','8365804')) )

GROUP BY wpecs_posts.ID ORDER BY wpecs_posts.post_date DESC LIMIT 0, 5;

You can see that this query is obviously way too heavy: Rows_sent: 5 Rows_examined:

366600…

After some examination we found out that the query was made through bbp_forum_functions.php – line 923 – function bbp_pre_get_posts_exclude_forums.

Has this problem ever occurred before? Is there someone with a solution?

Regards,

Tommy

[John James Jacoby]

Judging by the query, it looks like this is your topics index page or recent posts widget? It’s weird that it’s trying to grab both topics and replies, so that might be a bug depending on your config.

The short solution, which isn’t the best one, is not to have private or hidden forums. The NOT IN and INNER JOIN with the postmeta table is certainly to blame here, which goes away if you don’t have private or hidden forums. That combined with the IN query on ‘topic’ and ‘reply’ will pretty much result in full table scans.

The long term solution is to tidy up some of the meta values in bbPress, and move some of the hierarchical data out of postmeta and into indexed int values in the posts table. It’s a hack, but we’re at the mercy of the WordPress posts table schema.

A by-product of your post is it looks like it’s comparing as CHAR instead of SIGNED, which might marginally improve the query, but won’t fix the problem.