Activity query refactor for improved performance
|Reported by:||boonebgorges||Owned by:||johnjamesjacoby|
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.
Change History (13)
comment:6 johnjamesjacoby — 6 weeks ago
- Owner set to johnjamesjacoby
- Resolution set to fixed
- Status changed from new to closed