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

 Ok. We've got some real world testing of the patch now. First, it's a big
 improvement, but we're still running into issues whenever the queries
 aren't in cache. Specifically, we think this query is causing some
 problems:

 {{{
 SELECT a.*, u.user_email, u.user_nicename, u.user_login, u.display_name
 FROM [redacted]_bp_activity a LEFT JOIN [redacted]_users u ON a.user_id =
 u.ID
 WHERE a.type IN ( 'new_blog_post' ) AND a.hide_sitewide = 0
 AND a.type != 'activity_comment'
 ORDER BY a.date_recorded
 DESC LIMIT 0, 2
 }}}

 Reverse sorting the date_recorded field requires that the entire contents
 of the table be written out to a tmp table and then read back *prior* to
 the LIMIT being applied.

 I'm attaching processlist logs and and some mysql info. There is swap
 space shown as used but the disk wait time is 0%, indicating that swap
 space isn't actively being used. It's most likely caused by ntp or some
 other caching service on the system (known issues).

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