Get authors user query in-efficient when dealing with large numbers of users.

[l3rady]

When in WordPress admin the following query is run:

SELECT
	wp_users.ID,
	wp_users.user_login,
	wp_users.display_name
FROM wp_users
INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id)
WHERE
	1=1 AND
	(
		(
			wp_usermeta.meta_key = 'wp_user_level' AND
			CAST(wp_usermeta.meta_value AS CHAR) != '0'
		)
	)
ORDER BY display_name ASC;

This is getting a list of authors for the current site. Now this query is fine for small sites but is incredibly slow when dealing with large WP installations with thousands of users.

For example one of our largest WP installations has over 225K Users with over 7M usermeta records and the above query takes, on our server, over 34 seconds to complete. This is long enough for connections to timeout and the resulting data to not be cached and run over and over until the database queue is so long that PHP starts to crash.

Now the above query is generated deep within the WP_User_Query class and understand that the query is generated in such a way that allows a number of queries to be built dynamically, but the above query is very in-efficient. I've re written the query and my new query only takes 700 milliseconds to run.

SELECT
	wp_users.ID,
	wp_users.user_login,
	wp_users.display_name
FROM wp_usermeta
LEFT JOIN wp_users ON (wp_users.ID = wp_usermeta.user_id)
WHERE wp_usermeta.meta_key = 'wp_user_level' AND wp_usermeta.meta_value != '0'
HAVING wp_users.ID IS NOT NULL
ORDER BY display_name ASC;

I'm looking into how I can improve the queries in the WP_User_Query class but thought it would be good to bring this to the attention of some core devs.

[l3rady]

I have a quick fix for the this but is incredibly hacky:

/**
 * @var $q WP_User_Query
 */
add_action( 'pre_user_query', function ( $q ) {
	global $wpdb;

	if ( !isset( $q->query_where ) ) {
		return;
	}

	$where_match = 'WHERE 1=1 AND ( (' . $wpdb->usermeta . '.meta_key = \'' . $wpdb->prefix . 'user_level\' AND CAST(' . $wpdb->usermeta . '.meta_value AS CHAR) != \'0\') )';

	if ( $q->query_where !== $where_match ) {
		return;
	}

	$q->query_from  = 'FROM ' . $wpdb->usermeta . ' LEFT JOIN ' . $wpdb->users . ' ON (' . $wpdb->users . '.ID = ' . $wpdb->usermeta . '.user_id)';
	$q->query_where = 'WHERE ' . $wpdb->usermeta . '.meta_key = \'' . $wpdb->prefix . 'user_level\' AND ' . $wpdb->usermeta . '.meta_value != \'0\' HAVING ' . $wpdb->users . '.ID IS NOT NULL';
} );

[Protozoan]

@l3rady: I tried your patch, but still face problems.

Got >10k users (subscribers) and when I open a post in wp-admin it will take about 30 sec before the page loads.

Have you encountered the same problem? Any ideas how to resolve it? :)

[jmichaelward]

Hey all, I'm working on a client site with hundreds of thousands of users, and I've come across this slow query as a result. In my particular instance, it appears that WP_User_Query->query() gets called two times by the WordPress Core, and due to the number of users, it takes ~50-60 seconds to process each query (so, two minutes to fully load an edit page).

It looks like this has been in Trac for awhile, and the logs show some related issues. Any chance we can get some focus on this for 4.4?

[sgatz]

I'd like to +1 this. We're seeing this query take 10-25 seconds. So just to load the list of posts we have to wait forever. We require registration to comment, so we have lots of non-admin users (243,000+) and the wp-admin area is nearly impossible to use.

[brandonliles]

I made my first WP Plugin to hack around this. It basically just creates/maintains a cache table of author user_ids.

​https://github.com/bliles/wp-admin10x

[lukecavanagh]

@brandonliles

Thanks for sharing.

[toddlevy]

Replying to brandonliles:

I made my first WP Plugin to hack around this. It basically just creates/maintains a cache table of author user_ids.

​https://github.com/bliles/wp-admin10x

Thank you for creating and sharing this.

Question: will this remove deleted users from cache table?

Not seeing it in the code but perhaps I'm missing it.

Thinking something like this would to the trick...

add_action('deleted_user', function($id, $reassign)  {
  $a10x = new Admin10X();
  $a10x->del_author($id);
}, 10, 2);

[brandonliles]

Thanks for the suggestion @toddlevy ! Do you want to open a pull request on the github project? I'd be happy to review it and update the plugin.

[toddlevy]

Replying to brandonliles:

Thanks for the suggestion @toddlevy ! Do you want to open a pull request on the github project? I'd be happy to review it and update the plugin.

​Pull request created.

Found another smallish issue... since the Admin10X class doesn't get instantiated until pre_user_query or set_user_role gets called, the table never gets created and populated.

Don't have a "real" solve but I was able to work around it by sticking this at the top of the plugin when I activated... $a10x = new Admin10X(); ... and then commenting it out.

[brandonliles]

Thanks again @toddlevy! I've fixed the plugin installation/uninstallation issue, incorporated your changes and added you as a contributor!

[andreasanta]

Can you please include this in WP Core? It took me ages to debug on our site with 400k users and @brandonliles plugin is the only solution that worked. This would really prevent future users to loose lots of time profiling the issue. Thank you.

[OllieJones]

Rick James and I have released a plugin to change the indexes on wp_usermeta (and on other tables) to give a significant performance boost to the kind of query in this ticket.

Here's the plugin. ​https://wordpress.org/plugins/index-wp-mysql-for-speed/ Nothing would make us happier than for our plugin to be rendered obsolete by improvements in core or elsewhere.

Here's something I wrote for a related ticket. https://core.trac.wordpress.org/ticket/33885#comment:86

@SergeyBiryukov says they are looking at this indexing opportunity in the performance team. ​https://github.com/WordPress/performance/issues/132

All that being said, this WHERE clause CAST(wp_usermeta.meta_value AS CHAR) != '0' could be simpler, slightly faster, and still precisely equivalent, if it said simply wp_usermeta.meta_value != '0'.

[OllieJones]

I've just released another database speedup plugin. This one mitigates MySQL inefficiencies when handling tens of thousands of users (or more). Some may find it useful. ​https://wordpress.org/plugins/index-wp-users-for-speed/