Skip to:

bbPress 2.0.2 and 1.6MM posts = painfully slow

  • I was troubleshooting an issue with a rather large migration to bbPress (1.6MM posts) and bbPress 2.0.2 is very slow to load thread pages.

    It looks like bbPress (WP_Query) is trying to do an unindexed query lookup:

    CAST(wp_postmeta.meta_value AS CHAR) = '787852'

    The query takes quite some time and does a full table scan:

    # Time: 120518 19:23:53
    # User@Host: db[db] @ localhost []
    # Query_time: 9.688145 Lock_time: 0.000050 Rows_sent: 3 Rows_examined: 1623367
    SET timestamp=1337387033;
    SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type IN ('topic', 'reply') AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed') AND ( (wp_postmeta.meta_key = '_bbp_topic_id' AND CAST(wp_postmeta.meta_value AS CHAR) = '787852') ) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date ASC LIMIT 0, 25;

    Here’s a simple stack trace:

    /home/site/public_html/wp-includes/query.php   2498   get_sql ()   -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-includes/query.php 2913 get_posts () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-includes/query.php 3000 query () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-content/plugins/bbpress/bbp-includes/bbp-reply-template.php 129 __construct () -- /home/site/public_html/wp-content/plugins/bbpress/bbp-includes/
    /home/site/public_html/wp-content/themes/site/bbpress/content-single-topic.php 35 bbp_has_replies () -- /home/site/public_html/wp-content/themes/site/bbpress/
    /home/site/public_html/wp-includes/theme.php 1117 require () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-includes/theme.php 1091 load_template () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-includes/general-template.php 128 locate_template () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-content/plugins/bbpress/bbp-includes/bbp-core-compatibility.php 144 get_template_part () -- /home/site/public_html/wp-content/plugins/bbpress/bbp-includes/
    /home/site/public_html/wp-content/themes/site/single-topic.php 33 bbp_get_template_part () -- /home/site/public_html/wp-content/themes/site/
    /home/site/public_html/wp-includes/template-loader.php 43 include () -- /home/site/public_html/wp-includes/
    /home/site/public_html/wp-blog-header.php 16 require_once () -- /home/site/public_html/
    /home/site/public_html/index.php 17 require () -- /home/site/public_html/

    Here’s the $bbp_r query passed into WP_Query:

    [meta_query] => Array
    [0] => Array
    [key] => _bbp_topic_id
    [value] => 1623928
    [compare] => =


    [post_type] => Array
    [0] => topic
    [1] => reply

    [orderby] => date
    [order] => ASC
    [posts_per_page] => 25
    [paged] => 1
    [post_status] => publish,closed

Viewing 7 replies - 1 through 7 (of 7 total)

  • Gautam Gupta


    #1823 should address this.

    This seems to have the right idea, but I’m not sure it covers WP_Query queries where the search criteria is against a meta key. The query has to cast an unindexed full-text field to char across N rows to find matching values.

    The generated query fragment that is problematic is this:

    wp_postmeta.meta_key = '_bbp_topic_id'
    AND CAST(wp_postmeta.meta_value AS CHAR) = '787852'

    It’s a core issue since Threads are used to group up Posts.

    This looks like an issue with WP_Meta_Query issuing a CAST for all rows in wp_postmeta during the query.

    Relevant LOCs in meta.php:

    $where[$k] = ' (' . $where[$k] . $wpdb->prepare("CAST($alias.meta_value AS {$meta_type}) {$meta_compare} {$meta_compare_string})", $meta_value);

    There are several options to pursue:

    * Store thread ids in a separate table and intercept get_meta_sql filter to update the query to look in the correct table for _bbp_topic_id lookups.

    * Update WP_Meta_Query to read relevant types from type-specific tables (i.e. wp_postmeta_unsigned) for topic_id lookups. Requires updating mechanisms to save metadata into type-specific tables as well.

    What are some other options?

    John James Jacoby


    Yeah; the meta queries are really awful.

    I think I’ve resolved the issue. You’re one of the first people to report back using bbPress 2.x at that scale, so I’d love some feedback on the change.

    John, this is amazing! Thank you for fixing this.

    I’ll have to update my installation to your latest trunk before I can test.

    I will let you know how it works out.

    John, I did some preliminary tests and the results aren’t looking good.

    I left my comments on trac:

    Daniel J. Lewis


    Any update to this? I, too, have a large bbPress forum and I see the following in my error log:

    SlowTimer [6357ms] at runtime/ext_mysql: slow query: SELECT SQL_CALC_FOUND_ROWS  wp_13_posts.ID FROM wp_13_posts  INNER JOIN wp_13_postmeta ON (wp_13_posts.ID = wp_13_postmeta.post_id)\nINNER JOIN wp_13_postmeta AS mt1 ON (wp_13_posts.ID = mt1.post_id) WHERE 1=1  AND wp_13_posts.post_type = 'topic' AND (wp_13_posts.post_status = 'publish' OR wp_13_posts.post_status = 'closed') AND (wp_13_postmeta.meta_key = '_bbp_last_active_time'\nAND  (mt1.meta_key = '_bbp_forum_id' AND CAST(mt1.meta_value AS SIGNED) != '133643') ) GROUP BY wp_13_posts.ID ORDER BY wp_13_postmeta.meta_value DESC LIMIT 0, 25
Viewing 7 replies - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.
Skip to toolbar