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

 mym6 - Thanks for the results. I'm glad to have it confirmed that this is,
 in fact, an index problem.

 This all still doesn't explain why (1) the optimizer is making such a
 large error to begin with; (2) date_recorded is not showing up in your
 possible_keys; and (3) why USE INDEX is not enough to make MySQL use the
 index. Did you try FORCE INDEX in place of USE INDEX in my original patch?

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

 What do you mean by "this" and what do you mean by "fix"? Do you mean that
 you tried IGNORE INDEX on your COUNT query, and it's still slow?
 (zacechola said above that my original query hint patch helped a lot,
 though maybe I misinterpreted
 https://buddypress.trac.wordpress.org/ticket/4045#comment:8) The following
 query should, for optimal speed, be indexed by `type`:

 {{{
 SELECT count(*)
 FROM [redacted]_bp_activity a
 WHERE a.type IN ( 'new_blog_post' )
 AND a.hide_sitewide = 0
 AND a.type != 'activity_comment'
 }}}

 Are you saying that indexing by `type` doesn't actually make the query
 faster than letting the optimizer work, or that using IGNORE INDEX doesn't
 improve over USE INDEX?

 In any case, the COUNT(*) query is never going to run as fast as the LIMIT
 query. It's going to need a different kind of optimization, and probably
 some selective caching by BP itself. I'm going to work on that next.

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