get_users() causes a huge number of SQL queries causing 500 server errors

[dpacmittal]

Hey,

get_users() function causes a 500 server error if no arguments are passed, that is all user information is trying to be read. On my website with over 50k users, it causes ~15k SQL queries (checked using general query log). Atleast one plugin I know of uses get_users() without arguments (Events-manager). This causes the plugin to cause 500 server errors.

Ideally, if no arguments are passed, only a select * from {prefix}_users should be issued instead of 15k separate queries.

Here's a part from query log which shows individual SQL queries are being made:

		   59 Query	SELECT wp_users.* FROM wp_users WHERE 1=1 ORDER BY user_login ASC
130226  2:13:11	   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (26901)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (43704)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (43674)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (41395)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (41684)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (38434)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (36368)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (24303)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (44830)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (23256)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (44818)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (18293)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (20351)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (28186)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (26348)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (32837)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (21992)
		   59 Query	SELECT user_id, meta_key, meta_value FROM wp_usermeta WHERE user_id IN (45162)

[dpacmittal]

Sorry, I forgot to mention joins should be used. Atleast, TCP overhead is reduced along with other initializations that mysql does for each query (parsing, query compiling etc). This will significantly increase the performance of get_users(), IMHO.

[SergeyBiryukov]

Related: #15854

[SergeyBiryukov]

The query comes from update_meta_cache():
​http://core.trac.wordpress.org/browser/tags/3.5.1/wp-includes/meta.php#L556

Call stack:

get_users, WP_User_Query->__construct, WP_User_Query->query, WP_User->__construct, WP_User->init, WP_User->for_blog, WP_User->_init_caps, get_user_meta, get_metadata, update_meta_cache 

[nacin]

So maybe [22248] was wrong, and all and all_with_meta should be one and the same. This came out of #22057.

WP_Query already does multiple queries by default. Maybe all_without_meta (as in, an opt-out) is better here.

[dpacmittal]

But isn't multiple queries the real problem here?

[nacin]

Replying to dpacmittal:

But isn't multiple queries the real problem here?

Yes. all_with_meta caches usermeta all in one query.

Of course, getting 15k users in one go is going to cause a problem for you no matter what — it's just a ton of data to return back to PHP. You are likely going to want to do limits and offsets.

[johnbillion]

The WP_User_Query class is much more efficient than it used to be, so this should be less of a problem now.

Feel free to re-open if there are still performance concerns.