[buddypress-trac] [BuddyPress Trac] #5629: Slow queries with BP_Activity_Activity::get()

[buddypress-trac]

#5629: Slow queries with BP_Activity_Activity::get()
-------------------------+------------------
 Reporter:  Clean-Cole   |       Owner:
     Type:  enhancement  |      Status:  new
 Priority:  lowest       |   Milestone:  2.1
Component:  Activity     |     Version:  2.0
 Severity:  normal       |  Resolution:
 Keywords:  has-patch    |
-------------------------+------------------

Comment (by boonebgorges):

 5629.patch is a first pass at removing total counts from activity queries
 by default.

 There are a few main motivations at work here:

 1. Despite some big improvements in the efficiency of activity queries,
 COUNT queries are still slow. At scale, it's unlikely that we're going to
 be able to improve them much more - it's an unavoidable consequence of the
 sheer size of the tables.

 2. The 'total' value from the activity query is actually used very
 sparingly in BuddyPress, and my guess is that we can put a few workarounds
 in place to remove it altogether for the majority of uses.

 A few notes about the patch:

 a. One place where we do generally use the total count is in
 `bp_activity_has_more_items()`. My workaround is to do a little trick: if
 the per_page value is 20, query instead for 21 activity ids. If your
 results have more than 20 items, then `has_more_items` is true. I pass
 that value down through the template class, and use it in
 `bp_activity_has_more_items()` if it's available.
 b. The new `'count_total'` parameter for `BP_Activity_Activity::get()` is
 modeled after the same param in `BP_User_Query`. At the moment, if you
 pass false for activity, no query is done, while any other value will
 perform a separate COUNT query. But we could implement sometihng like
 `sql_calc_found_rows` if we thought that'd be helpful as well.

 The major problem here is noscript support. When javascript is disabled,
 we put up traditional pagination markup. But to build that pagination
 markup, we need total counts. The problem is that, when determining the
 default arguments for `bp_has_activities()`, there's no way to know
 whether javascript is turned off. A couple ideas: put an entirely separate
 activity loop into <noscript> tags; replace regular noscript pagination
 with a 'Load More' button that simply loads 'p=2'.

 In any case, this change will result in a huge increase in efficiency on
 many setups, so I think it's worth pursuing (possibly in connection with
 better count caching, as described above). Thoughts welcome.

--
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/5629#comment:6>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac