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

 Thanks for the preliminary feedback, zacechola. Glad to hear that the
 indexing trick helps.

 I'm going to dig more into the ORDER BY date_recorded issue later today
 (hopefully), but first I was hoping to get some more info from you:
 - Would you mind running an EXPLAIN query on the problematic query? I'm a
 bit confused as to why it's loading the entire table into memory when it
 should be using the date_recorded index
 - Do you find similar problems for all activity queries, or is it
 specifically those with a.type IN ( 'new_blog_post' )? Is there anything
 special to know about the distribution of different 'type' values in your
 activity database (like, is it mostly new_blog_post, or is it a pretty
 good distribution of many types)? SHOW INDEX FROM [redacted]_bp_activity
 would be helpful, so I can see cardinality, etc.

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