WordPress.org

Make WordPress Core

Opened 5 years ago

Last modified 2 years ago

#28160 new enhancement

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

Reported by: l3rady Owned by:
Milestone: Awaiting Review Priority: normal
Severity: major Version: 3.9
Component: Users Keywords:
Focuses: Cc:
PR Number:

Description

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.

Change History (12)

#1 @l3rady
5 years ago

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';
} );

#2 @Protozoan
5 years ago

@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? :)

Last edited 5 years ago by Protozoan (previous) (diff)

This ticket was mentioned in Slack in #core by boone. View the logs.


5 years ago

#4 @jmichaelward
4 years ago

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?

This ticket was mentioned in Slack in #core by jmichaelward. View the logs.


4 years ago

#6 @sgatz
3 years ago

  • Severity changed from normal to major

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.

#7 follow-up: @brandonliles
3 years ago

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

#8 @lukecavanagh
3 years ago

@brandonliles

Thanks for sharing.

#9 in reply to: ↑ 7 @toddlevy
2 years ago

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);

#10 follow-up: @brandonliles
2 years ago

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.

#11 in reply to: ↑ 10 @toddlevy
2 years ago

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.

#12 @brandonliles
2 years ago

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

Note: See TracTickets for help on using tickets.