WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 12 months ago

#23609 closed defect (bug) (worksforme)

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

Reported by: dpacmittal Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Users Keywords: needs-patch
Focuses: performance Cc:

Description

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)

Change History (13)

#1 @dpacmittal
4 years ago

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.

#2 @SergeyBiryukov
4 years ago

  • Component changed from General to Users

#4 @SergeyBiryukov
4 years ago

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 

#5 @nacin
4 years ago

  • Milestone changed from Awaiting Review to 3.6

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.

#6 follow-up: @dpacmittal
4 years ago

But isn't multiple queries the real problem here?

#7 in reply to: ↑ 6 @nacin
4 years ago

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.

#8 @alex-ye
4 years ago

  • Cc nashwan.doaqan@… added

#9 @ryan
4 years ago

  • Milestone changed from 3.6 to Future Release

#10 @idealien
4 years ago

  • Cc jamie@… added

#11 @non4eg
2 years ago

Last edited 2 years ago by non4eg (previous) (diff)

#12 @chriscct7
12 months ago

  • Focuses performance added
  • Keywords needs-patch added

#13 @johnbillion
12 months ago

  • Milestone Future Release deleted
  • Resolution set to worksforme
  • Status changed from new to closed

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.

Note: See TracTickets for help on using tickets.