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

 zacechola - Thanks so much for that info. It's really helpful. That said,
 I am a bit confused. Your SHOW INDEX results show an index on
 date_recorded, yet the query optimizer is not even identifying
 date_recorded as one of the possible_keys. That's why the paged query is
 going so slowly.

 I must admit I'm in a bit over my head here - I'm not sure why MySQL would
 be acting like this. But please do try 4045.02.diff, which is pretty much
 the same thing as 4045.01.diff, except that in the _paged_ query, {{{USE
 INDEX (date_recorded)}}} is specified. (Note that the patch had to be
 redone a bit to apply to some changes to trunk from this afternoon. Not
 sure if it will apply cleanly to a 1.5.4 install, but in any case it's
 easy enough to apply manually.) I am not sure whether MySQL will obey,
 given that it doesn't seem to recognize it as a possible key, but it's
 worth a try. (You might also try {{{FORCE INDEX (date_recorded)}}}.) You
 could also consider running {{{ANALYZE TABLE}}} on your activity table, to
 see if it jogs MySQL's memory.

 I'm going to do some more analysis to see why we are joining the users
 table here. At a glance, it doesn't look necessary. I don't think it's at
 the root of your extreme slowness, but every little bit helps.

 shanebp - Thanks for the data point :) If you're feeling brave, you could
 apply the changes to your 1.2.9 installation manually - the logic in the
 ::get() method is not hugely change. You'd need to add the index-selection
 block above line 131 in this file
 https://buddypress.trac.wordpress.org/browser/tags/1.2.9/bp-activity/bp-
 activity-classes.php#L123, and then change line 131 so that it includes
 {$index_hint_sql} right after {$from_sql}. You can also apply the
 date_recorded index manually, to line 95:
 https://buddypress.trac.wordpress.org/browser/tags/1.2.9/bp-activity/bp-
 activity-classes.php#L93. See 4045.02.diff for a guide (and BE CAREFUL! :)
 )

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