Editing Pages / Posts in wp-admin creates 50,000 SQL Queries

[wcvendors]

Crazy, I know! :)

My BuddyPress site has around 49,000 members. When I go to edit a page, or a post, as admin, there are 49,377 queries executing according to Query Monitor (a free WP Plugin).

The queries being loaded are one for every single member on the site.

98.
Time: 0.44 ms
Query: SELECT id, user_id, field_id, value, last_updated FROM wp_bp_xprofile_data WHERE field_id = 1 AND user_id IN (XXXXX) (XXXXX changes based on user ID being looked up
Function: require_once, require_once, do_action('admin_enqueue_scripts'), call_user_func_array, bp_admin_enqueue_scripts, do_action('bp_admin_enqueue_scripts'), call_user_func_array, bp_activity_mentions_script, do_action('bp_activity_mentions_prime_results'), call_user_func_array, bp_friends_prime_mentions_results, bp_core_get_user_displayname, bp_core_get_user_displaynames, BP_XProfile_ProfileData::get_value_byid

This means about 60-80 seconds to edit a single page or a single post in wp-admin. The rest of wp-admin is fine.

Latest WP and latest BP are installed.

When viewing the source of edit page or edit post, I see it is adding data from all of my "Friends" on the site (Which are 49,000 members, all members are automatic friends).

var BP_Suggestions = {"friends":[{"ID":"----","image":"http:\/\/gravatar.com\/avatar\/964cd5d52e2eb1c31427e49202ed5d16?d=http:\/\/www.DOMAIN.com\/wp-content\/themes\/buddyboss\/images\/wpb-avatar.png&s=50&r=X","name":"----"}

I dont know how to fix this, or why editing a post or a page wants to load my entire friends list

Any suggestions or advice or a bug fix would be GREATLY appreciated!

Regards,

Ben

[imath]

Hi wcvendors

Thanks for your feedback. I confirm a query is added for each friend you have.

i suggest to :

• Wait for DJPaul's opinion about it to see how we can deal with this in 2.2
• In the meantime, you can neutralize at mentions on your config by creating the ​bp-custom.php file at the root of /wp-content/plugins and put this ​code in it.

[imath]

bp_core_get_user_displayname() is generating the extra queries in bp_friends_prime_mentions_results()

5950.patch suggests to avoid these extra queries by using $user->display_name if it exists, else it defaults to user_nicename.

[DJPaul]

Few thoughts:

I think we need something like wp_is_large_network that checks if a user has "many" friends, and if so, we return out of bp_friends_prime_mentions_results. It's intended to only pre-cache friend lookups for quick results, but not to generate a large query, or if Friends are being used in a non-default way.

We can use $user->display_name instead of bp_core_get_user_displayname(...) if the xprofile sync is enabled (! bp_disable_profile_sync() ). This seems like a great improvement.

I am hesitant to use $user->user_nicename because I am pretty sure it isn't always the same as xprofile's name field. If you wanted to do this, I think we will need to create a whole lot of user accounts with different types of user name etc, and check which variant of those formats appear in the @mention picker UI, with and without username compatibility mode being enabled, to check that everything remains sane and logical. It's kind of a pain to do. :)

[r-a-y]

A short-term fix is to also disable suggestions support from the WP dashboard unless you're on the BP Groups admin page.

[imath]

Replying to DJPaul:

I think we need something like wp_is_large_network

I suggest to use friends_get_total_friend_count( $user_id ) in 5950.02.patch i put the limit at 100 friends, but we can increase this limit.

We can use $user->display_name instead of bp_core_get_user_displayname(...) if the xprofile sync is enabled (! bp_disable_profile_sync() ). This seems like a great improvement.

I use $user->display_name, if not set fallback to bp_core_get_user_displayname(...)

r-a-y, thanks for your feedback, you're right, the ​gist i shared with wcvendors was suggesting to disallow mentions scripts if on WP Admin till we fix this. I'm not sure we need to check for groups administration as filtering bp_activity_maybe_load_mentions_scripts to false in WP Admin shouldn't have an impact on the "add member auto-complete" feature.

[wcvendors]

Thank you everyone for the flurry of comments and suggestions on this.

Patching the admin side might work for editing posts/pages, of course, however when you view a members profile on the front-end, those still take a few minutes each to load. (PS -- This is a dedicated 24 core Xeon with 64GB Memory, and it only runs this site. I cringe to think of a smaller site on GoDaddy and the page load times!)

Disabling the "at" mentions all together on the site temporarily would set me on fire by the hell wrath of members who use it all the time on the forums, wall posts, and comments. ;) They'd kill me! :)

Disabling the mentions caching if friends list exceeds 250 (or so?) would be a pretty great idea.

[DJPaul]

Spoke with JJJ at WCSF and we decided is_large_network(users) will suffice (that's > 10k users) with the idea that anyone with a vast number of users and friends would probably want to implement the @mentions lookups in some other way. I think this is OK to try and see how it goes.

[wcvendors]

Sounds good to me. :)

[DJPaul]

Turns out aforementioned function is multisite only, which is very annoying.

[djpaul]

In 9101:

Mentions: for pre-cached result, use displayname if profile sync is enabled.

This should cut down on numbers of SQL queries made in certain
situations where a user on a site has many friends (whose user details
are not in WP’s object cache).

See #5950

[djpaul]

In 9102:

Mentions: don't prime mention results if the site/user has many users/friends.

Sites with huge amounts of users (and therefore probably many potential
friendships) can cause the primed result generation to be slow due to
the number of potential user name lookups/queries made.

Multisite has a function to help us decide if it’s a large network, but
it’s not available for regular WordPress, so we also check the logged
in user’s total friend count. (150 is Dunbar's number.)

In conjunction with the previous commit, this change should help make
the pre-cached result generation more performant.

Fixes #5950. Props imath for original patch.