Make WordPress Core

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#55223 closed enhancement (duplicate)

Caching this query can yield significant performance benefit for admin screen

Reported by: srikanthmeenakshi's profile srikanthmeenakshi Owned by:
Milestone: Priority: normal
Severity: normal Version: 5.9
Component: Users Keywords:
Focuses: performance Cc:

Description

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.

Change History (3)

#1 @swissspidy
3 years ago

  • Component changed from Query to Users

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.

#2 @srikanthmeenakshi
3 years ago

  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #40613.

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

#3 @SergeyBiryukov
3 years ago

  • Milestone Awaiting Review deleted

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

Note: See TracTickets for help on using tickets.