[wp-trac] [WordPress Trac] #28160: Get authors user query in-efficient when dealing with large numbers of users.

[WordPress Trac]

#28160: Get authors user query in-efficient when dealing with large numbers of
users.
-------------------------+-----------------------------
 Reporter:  l3rady       |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Users        |    Version:  3.9
 Severity:  normal       |   Keywords:
  Focuses:               |
-------------------------+-----------------------------
 When in WordPress admin the following query is run:

 {{{
 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_user_level' AND
                         CAST(wp_usermeta.meta_value AS CHAR) != '0'
                 )
         )
 ORDER BY display_name ASC;
 }}}

 This is getting a list of authors for the current site. Now this query is
 fine for small sites but is incredibly slow when dealing with large WP
 installations with thousands of users.

 For example one of our largest WP installations has over 225K Users with
 over 7M usermeta records and the above query takes, on our server, over 34
 seconds to complete. This is long enough for connections to timeout and
 the resulting data to not be cached and run over and over until the
 database queue is so long that PHP starts to crash.

 Now the above query is generated deep within the WP_User_Query class and
 understand that the query is generated in such a way that allows a number
 of queries to be built dynamically, but the above query is very in-
 efficient. I've re written the query and my new query only takes 700
 milliseconds to run.

 {{{
 SELECT
         wp_users.ID,
         wp_users.user_login,
         wp_users.display_name
 FROM wp_usermeta
 LEFT JOIN wp_users ON (wp_users.ID = wp_usermeta.user_id)
 WHERE wp_usermeta.meta_key = 'wp_user_level' AND wp_usermeta.meta_value !=
 '0'
 HAVING wp_users.ID IS NOT NULL
 ORDER BY display_name ASC;
 }}}

 I'm looking into how I can improve the queries in the WP_User_Query class
 but thought it would be good to bring this to the attention of some core
 devs.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/28160>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform