It seems this is a known issue for WordPress when ordering by meta query. BBPress does this to order by “last active” which is the default (and in my case, desired).

Loading a forum index, on my site, takes about 20 seconds.

Here is the offending SQL:

    [9] => Array
        (
            [0] => SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1  AND wp_posts.post_parent = 9  AND wp_posts.post_type = 'topic' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed' OR wp_posts.post_status = 'private' OR wp_posts.post_status = 'hidden') AND (wp_postmeta.meta_key = '_bbp_last_active_time' ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 15
            [1] => 19.448324918747
            [2] => require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), apply_filters('template_include'), call_user_func_array, bbp_template_include, apply_filters('bbp_template_include'), call_user_func_array, bbp_template_include_theme_compat, BBP_Shortcodes->display_forum, bbp_get_template_part, bbp_locate_template, load_template, require('/themes/xx/bbpress/content-single-forum.php'), bbp_has_topics, WP_Query->__construct, WP_Query->query, WP_Query->get_posts
        )

There’s a ticket which seems to be addressing my problem here: https://bbpress.trac.wordpress.org/ticket/1925

Unless I’m misreading it, the upshot is “We should probably be using post_modified on the topic instead of a meta value for ‘last activity’, so as to avoid using the JOIN… but we haven’t yet”.

So how do other sites do it? This one, for instance? Or WordPress.org? They have more posts than I do, but have got around this issue somehow.

Any solution?

Thanks.

[@duncanmarshall Participant 9 years, 10 months ago]

Anybody?

[@robkk Moderator 9 years, 10 months ago]

if your issue is really the same issue as the one in the trac ticket i think you would have to wait til bbpress 2.6 comes out.

[@duncanmarshall Participant 9 years, 10 months ago]

But then how on earth do all these other massive BBPress installations (like the one we’re on right now) cope?

[@robkk Moderator 9 years, 10 months ago]

@duncanmarshall

im not a developer of bbPress so i cant give you some technical details, all i can say is maybe its some damn good hosting that focus on speed.