[wp-trac] [WordPress Trac] #40613: Add query cache to WP_User_Query class

[WordPress Trac]

#40613: Add query cache to WP_User_Query class
-------------------------------------+------------------------------
 Reporter:  johnjamesjacoby          |       Owner:  (none)
     Type:  defect (bug)             |      Status:  new
 Priority:  normal                   |   Milestone:  Awaiting Review
Component:  Users                    |     Version:  3.1
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |     Focuses:  performance
-------------------------------------+------------------------------

Comment (by OllieJones):

 A similar issue crops up in the Gutenberg editor's REST request to
 populate the author dropdown.

 The REST request looks like this

 https://giantsite.example.com/wp-
 json/wp/v2/users?context=view&who=authors&per_page=50&_fields=id,name&_locale=user

 Enhancing its performance requires judicious filtering at the
 `'rest_user_query'` hook.

 (Just like handling the Posts and Pages panels requires judicious
 filtering on `'wp_dropdown_users_args'`.)

 Why are these slow? They need to examine each user's `wp_capabilities`
 metadata, which holds a serialized php object. It's expensive for MariaDB
 / MySQL to filter on this sort of predicate.

     WHERE ... meta_key = 'wp_capabilities' AND meta_value LIKE
 '%"author"%'

 for two reasons. One is the notorious `LIKE '%something'` dbms performance
 killer. The other is the fact that `meta_value` columns are CLOBs, which
 take extra work for the dbms to retrieve. And retrieve them it must, one
 for every user. In a megauser installation, that is *very* slow. It also
 hammers the dbms internal cache, potentially slowing down everything else.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/40613#comment:10>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform