slow query when loading wp-admin due to counting users in roles

[tharsheblows]

I have to admit straight up that my usermeta table is out of control and I need to stop WP from adding rows where the meta value is empty and clean it up.

So with that in mind, I am getting a very slow query (1.2s and rising) which thinks it's from the bbp_count_users function in /wp-content/plugins/bbpress/includes/users/functions.php:1531 but is actually from the WP count_users function here /wp-includes/user.php

Sometimes it's running twice, no clue why.

It's used in the function bbp_get_statistics in /wp-content/plugins/bbpress/includes/common/functions.php (used only on the dashboard and the user page) and is really just getting a count for the number of users with each role ie:

SELECT COUNT(NULLIF(`meta_value` LIKE '%\"administrator\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"editor\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"author\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"contributor\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"subscriber\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_keymaster\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_spectator\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_blocked\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_moderator\"%', false)), COUNT(NULLIF(`meta_value` LIKE '%\"bbp\\_participant\"%', false)), COUNT(*)
FROM wp_usermeta
WHERE meta_key = 'wp_capabilities' /* From [wp-admin/] in [/wp-content/plugins/bbpress/includes/users/functions.php:1531] */

I definitely need to do something about it and will probably take out the count_users() from bbp_total_users and instead keep a running count of the number of users / role in wp_options and use that, then put something to the tools page so I can recount when it gets messed up.

I know this is probably a WP issue but I thought I'd post here because this is where I'm going to change it. If you do think we should look at it in bbPress, I'll try to make a solution that's a little more robust and generalised.

[thebrandonallen]

Spent some time looking at this. Since we're only returning the total user count, count_users is overkill. The function docs even label it as a CPU intensive query. I'll look at patching this up, or @tharsheblows feel free to get at it.

We should probably use get_users here.

[johnjamesjacoby]

We can improve this right away in 2.6 with a large install check.

[johnjamesjacoby]

In 6542:

Statistics: Improvements to "At a Glance" integration.

• Prefer bbp_get_total_users() over count_users() to speed up loading
• Don't include empty array values
• Don't link to areas the current user cannot access
• Skip if current user cannot at least spectate (blocked or no access)

Fixes #2770.

[johnjamesjacoby]

In 6543:

Statistics: Define the $statistics array.

This change prevents debug notices, particularly in higher PHP versions (+7.0)

See #2770.