[buddypress-trac] [BuddyPress Trac] #7262: Activity comment tree should be fetched more efficiently

[buddypress-trac]

#7262: Activity comment tree should be fetched more efficiently
--------------------------+----------------------------
 Reporter:  boonebgorges  |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Future Release
Component:  Activity      |    Version:
 Severity:  normal        |   Keywords:  needs-patch
--------------------------+----------------------------
 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.

--
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/7262>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac