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

 I did not use a FORCE INDEX, that might have also worked I suppose.  And
 yes, forcing the index on the count query doesn't help.

 The optimizer is an odd thing.  I tried doing a query where I asked for at
 the MAX(date_recorded) and then asked for 50 days back from there, sorting
 the result set and then doing the limit.  It was very quick up to about 60
 days back, then it would start using the wrong index.  See:

 {{{

 EXPLAIN SELECT SQL_NO_CACHE 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'
   AND a.date_recorded
   BETWEEN  DATE_SUB((SELECT MAX(a.date_recorded)
                      FROM [redacted]_bp_activity a),
            INTERVAL 60 DAY)
     AND (SELECT MAX(a.date_recorded)
          FROM [redacted]_bp_activity a)
 ORDER BY a.date_recorded,a.type
 DESC LIMIT 0, 20\G
 }}}

 Would result in

 {{{
 *************************** 1. row ***************************
            id: 1
   select_type: PRIMARY
         table: a
          type: range
 possible_keys: date_recorded,type,hide_sitewide
           key: date_recorded
       key_len: 8
           ref: NULL
          rows: 489780
         Extra: Using where
 *************************** 2. row ***************************
            id: 1
   select_type: PRIMARY
         table: u
          type: eq_ref
 possible_keys: PRIMARY
           key: PRIMARY
       key_len: 8
           ref: [redacted].a.user_id
          rows: 1
         Extra:
 *************************** 3. row ***************************
            id: 3
   select_type: SUBQUERY
         table: NULL
          type: NULL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: NULL
         Extra: Select tables optimized away
 *************************** 4. row ***************************
            id: 2
   select_type: SUBQUERY
         table: NULL
          type: NULL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: NULL
         Extra: Select tables optimized away
 4 rows in set (0.00 sec)
 }}}

 But the following query:

 {{{
 EXPLAIN SELECT SQL_NO_CACHE 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'
   AND a.date_recorded
   BETWEEN  DATE_SUB((SELECT MAX(a.date_recorded)
                      FROM [redacted]_bp_activity a),
            INTERVAL 70 DAY)
     AND (SELECT MAX(a.date_recorded)
          FROM [redacted]_bp_activity a)
 ORDER BY a.date_recorded,a.type
 DESC LIMIT 0, 20\G
 }}}


 Would result in this

 {{{
 *************************** 1. row ***************************
            id: 1
   select_type: PRIMARY
         table: a
          type: ref
 possible_keys: date_recorded,type,hide_sitewide
           key: type
       key_len: 227
           ref: const
          rows: 1914714
         Extra: Using where; Using filesort
 *************************** 2. row ***************************
            id: 1
   select_type: PRIMARY
         table: u
          type: eq_ref
 possible_keys: PRIMARY
           key: PRIMARY
       key_len: 8
           ref: [redacted].a.user_id
          rows: 1
         Extra:
 *************************** 3. row ***************************
            id: 3
   select_type: SUBQUERY
         table: NULL
          type: NULL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: NULL
         Extra: Select tables optimized away
 *************************** 4. row ***************************
            id: 2
   select_type: SUBQUERY
         table: NULL
          type: NULL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: NULL
         Extra: Select tables optimized away
 4 rows in set (0.01 sec)
 }}}

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