[buddypress-trac] [BuddyPress] #4045: Activity Feed queries fail with millions of rows

[buddypress-trac at lists.automattic.com]

#4045: Activity Feed queries fail with millions of rows
-----------------------------------+--------------------
 Reporter:  zacechola              |       Owner:
     Type:  defect (bug)           |      Status:  new
 Priority:  normal                 |   Milestone:  1.6
Component:  Activity               |     Version:  1.5.4
 Severity:  normal                 |  Resolution:
 Keywords:  2nd-opinion has-patch  |
-----------------------------------+--------------------

Comment (by boonebgorges):

 cnorris23 - Thanks so much for the feedback. It's good to know that your
 testing more or less duplicated what I found in my own.

 Agreed about `SELECT FOUND_ROWS()`. My research showed that the
 performance of this function (much like `COUNT()` itself) is a general
 limitation in MySQL. In the end, actually, I found that there may just be
 a theoretical limit to how fast these `COUNT`s can be in general, because
 in the end there's no way to do them (with multiple `WHERE` clauses)
 without doing some sort of table scan. I even did some testing where I
 broke every `WHERE` clause into a subquery, in the hopes that it would
 take fuller advantage of indexing, but the performance differences seem to
 be pretty negligible.

 Making the count optional is pretty straightforward. The problem there is
 that you almost have to default to `count=true`, or you'll break existing
 implementations; and that means that people won't really experience the
 benefits unless they modify their theme files. I'm considering maybe
 introducing a BP corollary to `wp_is_large_network()`, which will detect
 (based on a simple activity row count, which is very fast in MyIASM)
 whether you have a huge number of items in your activity table, and then
 defaults to `count=false` accordingly (with relevant filters in place to
 override, of course). Anyway, this needs some experimentation. Would be
 happy to hear your feedback.

 There are some very tricky issues related to stashing stuff in transients.
 See https://buddypress.trac.wordpress.org/ticket/4076.  And in the end,
 regenerating these counts is going to be brutal on large databases even if
 it's only done once in a while - you can easily get into multiple
 *minutes* with large enough datasets. It would be pretty slick if there
 were a way to bust the transients asynchronously, so that it didn't
 interfere with pageload. Maybe hook something to WP's shutdown hook? Or do
 it with AJAX?

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