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

 Ok, I've been working zacechola to make this work.  Turns out that you
 should tell it which index to *ignore* rather than which index to use.
 This ensure's that the proper index is always picked.

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

 Will produce results virtually immediately.

 Explain on this will show

 {{{
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: a
          type: index
 possible_keys: NULL
           key: date_recorded
       key_len: 8
           ref: NULL
          rows: 3829415
         Extra: Using where
 *************************** 2. row ***************************
            id: 1
   select_type: SIMPLE
         table: u
          type: eq_ref
 possible_keys: PRIMARY
           key: PRIMARY
       key_len: 8
           ref: [redacted] a.user_id
          rows: 1
         Extra:
 2 rows in set (0.00 sec)
 }}}

 This doesn't fix the count(a.id) query though.

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