[wp-trac] [WordPress Trac] #55749: Large-site inefficiency in REST users endpoint used by Gutenberg editor

[WordPress Trac]

#55749: Large-site inefficiency in REST users endpoint used by Gutenberg editor
--------------------------+------------------------------------
 Reporter:  OllieJones    |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Users         |     Version:  trunk
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  rest-api, performance
--------------------------+------------------------------------
Changes (by SergeyBiryukov):

 * focuses:   => rest-api, performance


Old description:

> (In 6.0-RC2 and 5.9.3) The Gutenberg editor populates its dropdown list
> of authors by hitting this REST endpoint.
>

> {{{
> /wp-
> json/wp/v2/users?context=view&who=authors&per_page=50&_fields=id,name&_locale=user
> }}}
>
> This results in the following SQL statement.
>
> {{{
> 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_usermeta.meta_key = 'wp_user_level'
>     AND wp_usermeta.meta_value != '0' ))
> ORDER BY display_name ASC
> LIMIT 0, 50
> }}}
>
> There are some serious inefficiencies here.
>
> First, the notorious and deprecated performance killer
> {{{SQL_CALC_FOUND_ROWS}}} is present in the query. If the results of the
> query were going to be used for pagination (that is, a UI element like
> **<< < Page [1] of 250 pages > >>** then {{{SQL_CALC_FOUND_ROWS}}} would
> serve a purpose. But it doesn't in this usage of REST.
>
> Second,  the filter
>

> {{{
> (( wp_usermeta.meta_key = 'wp_user_level' AND wp_usermeta.meta_value !=
> '0' ))
> }}}
>
> correctly applies an index to get the right meta_keys from usermeta, but
> then it must scan the meta_values (they are CLOBs so the best we can do
> is prefix indexes).
>
> Third, {{{ORDER BY display_name LIMIT 50}}} means the DBMS must retrieve
> all the eligible users, sort them, and then discard all but 50.
>
> Similarly, when displaying the Posts or Pages dashboard panel this query
> prepopulates the dropdown list to use for authors in Quick Edit.
>
> {{{
> 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
> }}}
>
> As we say in New England USA, this is wicked slow. And each render of the
> Pages or Posts panel requires it to run.
>
> https://core.trac.wordpress.org/ticket/38741 provided some improvements
> to the handling of users on large many-user sites. But there's more to
> do.

New description:

 (In 6.0-RC2 and 5.9.3) The Gutenberg editor populates its dropdown list of
 authors by hitting this REST endpoint.


 {{{
 /wp-
 json/wp/v2/users?context=view&who=authors&per_page=50&_fields=id,name&_locale=user
 }}}

 This results in the following SQL statement.

 {{{
 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_usermeta.meta_key = 'wp_user_level'
     AND wp_usermeta.meta_value != '0' ))
 ORDER BY display_name ASC
 LIMIT 0, 50
 }}}

 There are some serious inefficiencies here.

 First, the notorious and deprecated performance killer
 {{{SQL_CALC_FOUND_ROWS}}} is present in the query. If the results of the
 query were going to be used for pagination (that is, a UI element like
 **<< < Page [1] of 250 pages > >>** then {{{SQL_CALC_FOUND_ROWS}}} would
 serve a purpose. But it doesn't in this usage of REST.

 Second,  the filter


 {{{
 (( wp_usermeta.meta_key = 'wp_user_level' AND wp_usermeta.meta_value !=
 '0' ))
 }}}

 correctly applies an index to get the right meta_keys from usermeta, but
 then it must scan the meta_values (they are CLOBs so the best we can do is
 prefix indexes).

 Third, {{{ORDER BY display_name LIMIT 50}}} means the DBMS must retrieve
 all the eligible users, sort them, and then discard all but 50.

 Similarly, when displaying the Posts or Pages dashboard panel this query
 prepopulates the dropdown list to use for authors in Quick Edit.

 {{{
 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
 }}}

 As we say in New England USA, this is wicked slow. And each render of the
 Pages or Posts panel requires it to run.

 #38741 provided some improvements to the handling of users on large many-
 user sites. But there's more to do.

--

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