WordPress.org

Make WordPress Core

Opened 2 years ago

Last modified 10 months ago

#23609 new defect (bug)

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

Reported by: dpacmittal Owned by:
Milestone: Future Release Priority: normal
Severity: normal Version:
Component: Users Keywords:
Focuses: 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 (11)

comment:1 @dpacmittal2 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.

comment:2 @SergeyBiryukov2 years ago

  • Component changed from General to Users

comment:4 @SergeyBiryukov2 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 

comment:5 @nacin2 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.

comment:6 follow-up: @dpacmittal2 years ago

But isn't multiple queries the real problem here?

comment:7 in reply to: ↑ 6 @nacin2 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.

comment:8 @alex-ye2 years ago

  • Cc nashwan.doaqan@… added

comment:9 @ryan2 years ago

  • Milestone changed from 3.6 to Future Release

comment:10 @idealien23 months ago

  • Cc jamie@… added

comment:11 @non4eg10 months ago

Last edited 10 months ago by non4eg (previous) (diff)
Note: See TracTickets for help on using tickets.