Activity comment tree should be fetched more efficiently

[boonebgorges]

In activity loops, we fetch the comments belonging to each comment in a linear fashion that follows the leaf nodes:

...
// top-level activity IDs are A, B, C
SELECT id FROM wp_bp_activity WHERE type = 'activity_comment' AND item_id = A // returns A1, A2
SELECT id FROM wp_bp_activity WHERE type = 'activity_comment' AND item_id = A1 // returns A1a
SELECT id FROM wp_bp_activity WHERE type = 'activity_comment' AND item_id = A1a // return nothing, so back up the tree
SELECT id FROM wp_bp_activity WHERE type = 'activity_comment' AND item_id = A2
// etc 

For complex trees, this can mean dozens or hundreds of queries.

In WP 4.4, comment queries were rewritten to avoid a similar pattern. The new strategy is to fetch an entire level of comments at a time:

// top-level activity IDs are A, B, C
SELECT id FROM wp_bp_activity WHERE type = 'activity_comment' AND item_id IN ( A, B ) // returns A1, A2, B1, B2
SELECT id FROM wp_bp_activity WHERE type = 'activity_comment' AND item_id IN ( A1, A2, B1, B2 ) // returns A1a, B2a
SELECT id FROM wp_bp_activity WHERE type = 'activity_comment' AND item_id IN ( A1a, B2a )
// etc, until a query returns empty - ie, we've got the whole tree

See WP_Comment_Query::fill_descendants(). Combined with query-level caching for the activity component, this change should mean a huge improvement in performance for activity queries.

[DJPaul]

We're closing this ticket because it has not received any contribution or comments for at least two years. We have decided that it is better to close tickets that are good ideas, which have not gotten (or are unlikely to get) contributions, rather than keep things open indefinitely. This will help us share a more realistic roadmap for BuddyPress with you.

Everyone very much appreciates the time and effort that you spent sharing your idea with us. On behalf of the entire BuddyPress team, thank you.

If you feel strongly that this enhancement should still be added to BuddyPress, and you are able to contribute effort towards it, we encourage you to re-open the ticket, or start a discussion about it in our Slack channel. Please consider that time has proven that good ideas without contributions do not get built.

For more information, see ​https://bpdevel.wordpress.com/2018/01/21/our-awaiting-contributions-milestone-contains/
or find us on Slack, in the #buddypress channel: ​https://make.wordpress.org/chat/