I added this ticket, which needs more conversation on approach:
https://bbpress.trac.wordpress.org/ticket/1925
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
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.
Thanks John. Is there anything I can do to help? My site build out has been on hold pending a fix for the slowness issue. If you have a preferred way of fixing it, perhaps I can help implement it.
Here were my original findings: https://bbpress.org/forums/topic/bbpress-202-sql_calc_found_rows-and-16mm-posts-painfully-slow
Edit: I’ve created a trac ticket for this: https://bbpress.trac.wordpress.org/ticket/1885
Does 2.1 RC2 remedy slowness issues on installations with more than a million 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?
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.
