Caching this query can yield significant performance benefit for admin screen

[srikanthmeenakshi]

Hi, Whenever the posts or pages listings are accessed from the admin dashboard, this query gets run:

SELECT wp_prefix_users.id,
       wp_prefix_users.user_login,
       wp_prefix_users.display_name
FROM   wp_prefix_users
       INNER JOIN wp_prefix_usermeta
               ON ( wp_prefix_users.id = wp_prefix_usermeta.user_id )
WHERE  1 = 1
       AND ((( ( wp_prefix_usermeta.meta_key = 'wp_prefix_capabilities'
                 AND wp_prefix_usermeta.meta_value LIKE '%\"edit\\_posts\"%' )
                OR ( wp_prefix_usermeta.meta_key = 'wp_prefix_capabilities'
                     AND wp_prefix_usermeta.meta_value LIKE
                         '%\"administrator\"%' )
                OR ( wp_prefix_usermeta.meta_key = 'wp_prefix_capabilities'
                     AND wp_prefix_usermeta.meta_value LIKE '%\"editor\"%' )
                OR ( wp_prefix_usermeta.meta_key = 'wp_prefix_capabilities'
                     AND wp_prefix_usermeta.meta_value LIKE '%\"author\"%' )
                OR ( wp_prefix_usermeta.meta_key = 'wp_prefix_capabilities'
                     AND wp_prefix_usermeta.meta_value LIKE '%\"contributor\"%'
                   )
                OR ( wp_prefix_usermeta.meta_key = 'wp_prefix_capabilities'
                     AND wp_prefix_usermeta.meta_value LIKE
                         '%\"shop\\_manager\"%' )
ORDER  BY display_name ASC 

This query with multiple 'like' clauses performs terribly. And it does not look like the results are cached. Considering that this is a static query whose results are unlikely to change frequently, this is an ideal query for caching and improving the performance of page and posts listings.

Thanks.

[swissspidy]

Thanks for opening this.

For context, this more complex query was introduced in #16841, but it was slow even before that.

Caching the result of this query would mean doing that in wp_dropdown_users(), a function that is known not to be scalable. See #40613 and #19867.

[srikanthmeenakshi]

Duplicate of #40613.

Thanks @swissspidy ! I am not surprised that this issue surfaced before :-) I hope a remedy is found and applied soon. Thanks!

[SergeyBiryukov]

Welcome to WordPress Trac and thanks for the follow-up :)