[buddypress-trac] [BuddyPress Trac] #5349: Activity query refactor for improved performance

[buddypress-trac]

#5349: Activity query refactor for improved performance
--------------------------+-----------------
 Reporter:  boonebgorges  |      Owner:
     Type:  task          |     Status:  new
 Priority:  normal        |  Milestone:  2.0
Component:  Activity      |    Version:
 Severity:  normal        |   Keywords:
--------------------------+-----------------
 On large BuddyPress sites, the activity table can grow very large very
 quickly, and the corresponding queries can be problematically slow. (See
 #4045, #4060, #5210 for some background.)

 With some of the recent improvements we've made to member queries (our
 previous bugaboo), I think we're well-poised to rethink how activity
 queries work.

 My proposed strategy is to split the query. Our current activity queries
 fetch entire rows, and includes an awful join against the global users
 table. When combined with DISTINCT and ORDER BY keywords, this behavior
 introduces serious scaling problems. By splitting into two queries - one
 to get a list of matching activity IDs, and another to get all related
 info - we can improve performance dramatically. See
 https://core.trac.wordpress.org/ticket/18536 for a similar discussion and
 solution implemented in WP_Query in WP 3.4.

 A very significant side effect of a split query is that it leads naturally
 to a split strategy with respect to activity caching: caching for complex
 queries as a separate process from caching single activity items. We
 currently do basically zero activity caching (I have some ideas that I'll
 put in another ticket, which I'll link from this one), but the change
 proposed here will begin the process.

 The most serious concern about this change is that we'll probably break
 plugins that are directly filtering the SQL queries. We can lessen the
 blow by (a) doing this early in a dev cycle and warning devs about the
 change, and (b) making sure there's a filter that allows a site to use
 legacy queries/filters. We've done this for `BP_User_Query` and I've heard
 very few complaints.

 I'm putting this out for discussion right now. I'm planning to work on
 some patches and benchmarks for early in the 2.0 cycle. Preliminary
 feedback welcome at any time.

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