[vibol]

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:

Array

(

[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

)

[Gautam Gupta]

#1823 should address this.

[vibol]

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.

[vibol]

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.

https://bbpress.trac.wordpress.org/changeset/4059

[vibol]

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.

[vibol]

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

I left my comments on trac: https://bbpress.trac.wordpress.org/ticket/1885#comment:3

[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