Database query needs optimization - BP_Core_User->get_users

[arpit.tambi.in]

When a website has large number of users (busy site), the database queries made by get_users function in BP_Core_User class take much longer to execute and CPU really spikes up bringing the entire website down. This class is located in bp-core-classes.php

Here is the sample info from mysql slow log

# Query_time: 27  Lock_time: 3  Rows_sent: 1  Rows_examined: 154595
SELECT COUNT(DISTINCT u.ID) FROM wp_users u LEFT JOIN wp_usermeta um ON um.user_id = u.ID LEFT JOIN wp_bp_xprofile_data spd ON u.ID = spd.user_id WHERE u.user_status = 0 AND um.meta_key = 'last_activity' AND spd.value LIKE '%%United States%%' ORDER BY u.ID DESC;

[boonebgorges]

At least part of the slowness is coming from your join against xprofile for the '%%United States%%' value. You must be doing that with a plugin - BP does not do that out of the box.

That said, when we do counts, we should not be joining against usermeta for last activity - that information is not useful for purposes of a count. On the other hand, if we only mean to count users who have this meta value (can't remember if that's intended), we could just count DISTINCT(um.user_id), without the join against wp_users. It would probably break plugins like the one you're using if we made this latter change (as there would be no u.ID for your ON clause), so we can't do it for this release - needs some more architecting.

[arpit.tambi.in]

Yes you are right, I am not using a plugin, but a simple bp_has_members loop in my theme. I wanted to add a "Friend Suggestions" like feature on the sidebar, so I just added following to the sidebar -

bp_has_members(	array(
		'type'			  => 'newest',
		'per_page'        => 5,
		'max'             => 5,
		'search_terms'    => bp_get_member_profile_data(array( 'field' => 'Country', 'user_id' => bp_displayed_user_id())), // Pass search_terms to filter users by their profile data
	)

This code tries to find profiles with same country as the displayed user.

My wp_usermeta table has about 1M rows. wp_users has about 60K rows and wp_bp_xprofile_data has about 65K rows.

I also want to mention unusually high status values from mysql server -
Handler_read_rnd 62.5 M
Handler_read_rnd_next 261.5 M

I am not sure if its related to buddypress but still may be of some help.

[arpit.tambi.in]

I have more to say, I found that this query is being used as basic member/profile search in buddypress. Urls like these - ​http://www.example.com/members/?s=Arkansas make use of this query.

Each time the query is executed, mysqld CPU usage spikes up for sometime.

[DJPaul]

Addressed in bbPress 1.6 with Boone's awesome improvements to the xProfile queries!