[buddypress-trac] [BuddyPress Trac] #5629: Slow queries with BP_Activity_Activity::get()
noreply at wordpress.org
Mon May 26 18:34:25 UTC 2014
#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
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.
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
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/>
More information about the buddypress-trac