[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):

 Pasting the following comment from #4060, which belongs here:

 ==

 I see two queries that could cause concurrency bottlenecks; both of these
 are showing up in the processlist many times per minute:

 Query 1:

  SELECT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name
   FROM wp_bp_activity a USE INDEX (date_recorded) LEFT JOIN wp_users u
 ON a.user_id = u.ID WHERE a.user_id IN ( 28221 ) AND a.component
 IN ( 'album' ) AND a.item_id IN ( 47715 ) AND a.type != 'activity_comment'
  ORDER BY a.date_recorded DESC LIMIT 0, 20

 This query performs a 0.7 second filesort and I see this query run many
 times. Removing the USE INDEX (date_recorded) portion of this query
 brought the total run time down to around 1 millisecond. I recommend
 removing this portion of the query from your codebase.

 ==

 I will need to do some testing here. I assume that what's happening is
 that bp-album is filtering the query, since if the WHERE clause had looked
 like that to begin with, date_recorded would not have been selected for
 the index hint.

 shanebp, can you tell me what version of bp-album you're running, so I can
 check their codebase?

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