Make WordPress Core

Opened 11 years ago

Closed 8 years ago

#23609 closed defect (bug) (worksforme)

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

Reported by: dpacmittal's profile 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
11 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
11 years ago

  • Component changed from General to Users

#4 @SergeyBiryukov
11 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
11 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
11 years ago

But isn't multiple queries the real problem here?

#7 in reply to: ↑ 6 @nacin
11 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
11 years ago

  • Cc nashwan.doaqan@… added

#9 @ryan
11 years ago

  • Milestone changed from 3.6 to Future Release

#10 @idealien
11 years ago

  • Cc jamie@… added

#11 @non4eg
10 years ago

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

#12 @chriscct7
8 years ago

  • Focuses performance added
  • Keywords needs-patch added

#13 @johnbillion
8 years 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.