[buddypress-trac] [BuddyPress Trac] #7500: Harmful bp_activity indexes

[buddypress-trac]

#7500: Harmful bp_activity indexes
--------------------------+------------------------------
 Reporter:  brandonliles  |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Core          |     Version:
 Severity:  normal        |  Resolution:
 Keywords:  2nd-opinion   |
--------------------------+------------------------------

Comment (by brandonliles):

 Hi @johnjamesjacoby ,

 On our site we have a little over 1.6 million rows in the bp_activity
 table. Most of the time MySQL was using a decent query plan, but several
 times an hour we were seeing MySQL use the hide_sitewide and is_spam
 indexes for the activity loop which was about the same performance as a
 table scan. On our system that was resulting in a query that averaged 6
 seconds. In the graph I've posted here you see the result of our index
 adjustment. With those indexes removed, we're no longer seeing that bad
 query plan.

--
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/7500#comment:4>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac