Performance issues with the wp_bp_activities table

Hi,
We’re running into some performance issues with a couple of sites using BuddyPress 1.9.2 with WordPress 3.8.1 on our systems.

The problem query seems to be called from the wp-activity-classes.php. At first glance of just the query itself, it seems that the explicit SQL HINT in the query is the culprit. Here’s a gist explaining it..

<script src=”https://gist.github.com/vidluther/9370018.js”></script>

I don’t know the entire code base, or the history behind the decision to force SQL hints, so I wanted to ask here, before taking it to Trac.

For the record, I’ve tried the same query on 3 separate machines, ranging from 128GB RAM, to 48GB RAM.. With the hint, I’ve never been able to get the query to run in less than 1 second.

Running MySQL server from Percona version 5.5, and MySQL Community edition 5.6.

We’ve made some massive improvements for 2.0 in the activity query, but we haven’t really changed the way that this COUNT query works. In any case, forcing an index on a COUNT that doesn’t need sorting doesn’t really make much sense anyway, so we should probably remove it. If you get a chance, please open an enhancement ticket at https://buddypress.trac.wordpress.org with the suggestion. If not, I’ll try to get to it later.