[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  |
-----------------------------------+--------------------
Changes (by boonebgorges):

 * keywords:   => 2nd-opinion has-patch


Comment:

 Hi all. I have done a bunch of digging and experimentation, and I have
 made some progress.

 First, I have found that the real problem arises on the count() queries
 that get the total_activities values. That's because the paged query
 indexes properly on date_recorded. I found that it scales pretty well up
 to several million rows. The total_activities query, on the other hand, is
 really dumb about the way it selects an appropriate index.

 I've written a small patch that takes the following approach: it defines a
 preferred order of indexes, and then provides an index hint accordingly.
 So, for example, on the main Activity directory, the query optimizer is
 deciding to index by hide_sitewide, which hardly cuts down the results at
 all; with my patch, it prefers type (as in type != 'activity_comment').
 Likewise, on single group pages, it prefers item_id, and on single user
 pages it prefers user_id.

 zecechola and shanebp, I'm especially interested to hear if you have any
 luck with the patch (or suggestions for other courses of action). If this
 route seems promising, I think there are even more progressive changes
 that we can make; but this would make for a nice start.

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