REST API: Default query for users endpoint doesn't scale

[ocean90]

The user query is performed with the has_published_posts argument which generates the following query

SELECT SQL_CALC_FOUND_ROWS wp_users.* FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) WHERE 1=1 AND wp_users.ID IN ( SELECT DISTINCT wp_posts.post_author FROM wp_posts WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type IN ( 'post', 'page', 'attachment', 'forum', 'topic', 'reply' ) ) AND ( wp_usermeta.meta_key = 'wp_2_capabilities') ORDER BY display_name ASC LIMIT 0, 10

'forum', 'topic', and 'reply' are bbPress' post types. We use bbPress on wordpress.org/support/ where I noticed in the logs that the server/database can't handle the request.

I'm currently not sure how and if this needs to be fixed but having at least a ticket for it might help others.

[joehoyle]

@ocean90 I've set this to Future, as 4.7 is upon us, as by the description, we don't necessarily know if we can / should fix something here.

I'm not sure of a good way to fix this, unless there's some obvious optimization of the SQL query that I'm not seeing.

[dd32]

This is the kind of thing that needs to use wp_large_network( 'users' ) and find some other way of populating the index.

The first two options that come to mind are:

• "Sorry, Can't list users" error
• only listing say, the authors of the last 10k posts

Both of these options are pretty bad for what the endpoint wants to do, but there's very little choice here.

[aaroncampbell]

In 39843:

REST API: Change which users are shown in the users endpoint.

Only show users that have authored a post of a post type that has show_in_rest set to true.

Props rachelbaker, jnylen0.
See #38878.

[jnylen0]

In 39913:

REST API: Improve test coverage of single user endpoint for public data.

Add test coverage for requests of a single user resource for authors of post types registered as:

• public = true, show_in_rest = true: success without auth.
• public = true, show_in_rest = false: fail without auth.
• public = false, show_in_rest = true: success without auth.
• public = false, show_in_rest = false: fail without auth.

See #38878.
Fixes #39546.

[TimothyBlynJacobs]

Is this an issue that large sites are still experiencing? If so, is anyone aware of what patches people have put in place?

Adding a limit to the number of posts to check on a large network seems like a good choice if it is. Should that be something automatically added when building the query in WP_Query or should another WP_Query argument be exposed?