Make WordPress Core

Opened 7 years ago

Closed 11 months ago

Last modified 5 months ago

#39090 closed enhancement (fixed)

Remove duplicate query from posts page in the admin

Reported by: rodrigosprimo's profile rodrigosprimo Owned by:
Milestone: 6.3 Priority: normal
Severity: normal Version:
Component: Users Keywords: needs-patch needs-unit-tests
Focuses: performance Cc:

Description

The posts page in the admin (/wp-admin/edit.php) is running twice the query below. This is a problem specially for sites with a significant number of users since this query can be super slow (see #19867 and #28160).

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 wp_usermeta.meta_value != '0' ) )
ORDER BY display_name ASC

The query is executed by WP_User_Query::query() and, in this particular page, is called by the function wp_dropdown_users().

The attached patch fixes this problem by adding the result to the cache when the query is run the first time. Preventing multiple executions of the same query.

Attachments (1)

39090.patch (1.0 KB) - added by rodrigosprimo 7 years ago.

Download all attachments as: .zip

Change History (4)

@rodrigosprimo
7 years ago

#1 @boonebgorges
7 years ago

  • Component changed from Posts, Post Types to Users
  • Focuses administration removed
  • Keywords needs-patch needs-unit-tests added
  • Milestone changed from Awaiting Review to Future Release

@rodrigosprimo Thanks for the patch!

As written, the patch won't work - it caches queries indefinitely. This means that cached data will be returned even after (say) a new user registers. So we need some invalidation built in. The strategy we use in other query classes is an incrementor hashed with the cache key:

$last_changed = wp_cache_get_last_changed( 'users' );
$hashed_args = md5( $args );
$cache_key = "get_users:$hashed_args:$last_changed";

Then, on any action that will result in user query results being different (new user creation, user edit, etc), invalidate the cache: wp_cache_set( 'last_changed', microtime(), 'users' )

This may be a good time to break the user query into two separate queries: one for IDs only (which will be cached as described above), and one for user objects (which are already separately cached).

We'll need unit tests demonstrating invalidation in order to move forward.

#2 @spacedmonkey
11 months ago

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

This is now fixed, now that #40613 is committed.

#3 @joemcgill
5 months ago

  • Milestone changed from Future Release to 6.3
Note: See TracTickets for help on using tickets.