I have a rather large user db on my WordPress database (around 1m).

I am seeing a slow query on my log and it looks like this:

SELECT wp_users.ID,wp_users.user_login,wp_users.display_name FROM wp_users INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id ) WHERE 1=1 AND ( 
  ( 
    ( 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"edit\\_posts\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"administrator\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"editor\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"author\"%' ) 
      OR 
      ( wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%\"contributor\"%' )
    )
  )
) ORDER BY display_name ASC;

I think it is made by wp-includes/class-wp-user-query.php. I feel like this is a query to populate some dropdown with all the administrator accounts, but for the life of me I cannot find out what triggers it.

I want to find what is triggering it and stop it from executing this query. (Listing admins is not something I'm intending on using.

CodePudding user response：

There are two places where this query gets used:

1. when preparing the dropdown list of authors for use in the posts and pages Quick Edit functionality.
2. in the REST API when preparing the choice of authors for the Gutenberg editor page.

Here are a couple of snippets of code (this is a hack) to constrain those lists of authors to the first twenty wp_users.ID values. The query times will go from ridiculous to quick.

For the REST user query list:

add_filter( 'rest_user_query', 'constraint_rest_user_query', 10, 2 );
/**
 * Filters WP_User_Query arguments when querying users via the REST API.
 *
 * @link https://developer.wordpress.org/reference/classes/wp_user_query/
 *
 * @since 4.7.0
 *
 * @param array $prepared_args Array of arguments for WP_User_Query.
 * @param WP_REST_Request $request The REST API request.
 */
function constraint_rest_user_query( $prepared_args, $request ) {
  if ( $request->get_param( 'context' ) === 'view' 
       && $request->get_param( 'who' ) === 'authors' ) {
    $prepared_args['include'] = range( 0, 20 );
  }

  return $prepared_args;
}

For the posts and pages Quick Edit dropdown:

add_filter( 'wp_dropdown_users_args', 'constrain_wp_dropdown_users_args', 10, 2 );
/**
 * Filters the query arguments for the list of users in the dropdown.
 *
 * @param array $query_args The query arguments for get_users().
 * @param array $parsed_args The arguments passed to wp_dropdown_users() combined with the defaults.
 *
 * @returns array Updated $query_args
 * @since 4.4.0
 *
 */
function constrain_wp_dropdown_users_args( $query_args, $parsed_args ) {
  $query_args['include'] = range( 0, 20 );

  return $query_args;
}

If your contributors / authors / editors / administrators aren't among the first twenty user IDs on your site, you can use an array containing a list of their user IDs in place of range( 0, 20 ).