[buddypress-trac] [BuddyPress] #4076: Use transients for expensive activity queries

[buddypress-trac at lists.automattic.com]

#4076: Use transients for expensive activity queries
--------------------------+-----------------------------
 Reporter:  boonebgorges  |       Owner:
     Type:  enhancement   |      Status:  new
 Priority:  normal        |   Milestone:  Future Release
Component:  Activity      |     Version:
 Severity:  normal        |  Resolution:
 Keywords:  dev-feedback  |
--------------------------+-----------------------------

Comment (by boonebgorges):

 Thanks for the feedback, DJPaul.

 Good point about clearing them. In an ideal world, WP would have a
 straightforward way to get a list of transient keys; we could then loop
 through them, and, based on prefix, clear them out. Something like:

 {{{
 $prefix = 'bp_activity_' . $item_type . '_' . $item_id;

 $transient_keys = wp_get_transient_keys();
 foreach( $transient_keys as $key ) {
   if ( false !== strpos( $key, $prefix ) ) {
     delete_transient( $key );
   }
 }
 }}}

 We could, in theory, roll this ourselves (by looking either in the options
 table or in wp_cache, depending on configuration), but it seems hackish.

 That said, even if we had to do something less elegant and more piecemeal
 like storing only a small whitelist of query types, I think it's worth
 considering, for a couple reasons:
 - The vanilla first-20 queries for the main stream, individual users, and
 individual groups probably constitute a huge percentage of the total
 number of activity queries on any given site, especially since groups and
 users default to the activity component. So even just doing them could
 make a huge dent in the performance problems.
 - In my experience and experiments (which is admittedly limited), more
 complex queries (like those that filter on `type`, `user_id`, etc) are
 actually much faster than the simpler queries, because the indexes used
 are able to filter out a huge percentage of large datasets. When the only
 WHERE clauses are {{{hide_sitewide = 0 AND is_spam != 1}}}, the indexes
 are nearly useless. So this suggests that the more specific kinds of
 queries aren't as badly in need of caching attention.

 > You want to have this as a kind of level of template loop caching,
 rather than at a lower level in bp_activity_get()

 Actually, it wouldn't make a difference if it was at bp_activity_get() or
 bp_has_activities(). My only point is that it should be at the level of
 the complex queries, rather than (just) the level of individual activity
 item caching. So you're probably right that it would be fine there. The
 pressure points here are the paged_activities and total_activities
 queries, which get very very expensive on large datasets. Good point about
 people messing with BP_Activity_Template and the $activities_template
 global - you're probably right that we should be wary there.

 > What would happen to existing wp_cache in bp_activity_get()?

 Glad you brought that up. Actually, my idea is basically to create a much
 more sophisticated version of that cache. IMO, bp_activity_sitewide_front
 caches should be switched to transients anyway, again because it will have
 more immediate benefit for more different kinds of users.

-- 
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/4076#comment:2>
BuddyPress <http://buddypress.org/>
BuddyPress