[wp-trac] [WordPress Trac] #38741: Introduce the concept of a large site in order to speed up the Users screen when there are many users

[WordPress Trac]

#38741: Introduce the concept of a large site in order to speed up the Users screen
when there are many users
-------------------------+-------------------------------------------------
 Reporter:  johnbillion  |       Owner:  spacedmonkey
     Type:  enhancement  |      Status:  closed
 Priority:  normal       |   Milestone:  6.0
Component:  Users        |     Version:
 Severity:  normal       |  Resolution:  fixed
 Keywords:  has-patch    |     Focuses:  administration, multisite,
                         |  performance
-------------------------+-------------------------------------------------

Comment (by OllieJones):

 I have just completed work on a plugin for handling large numbers of
 users. https://wordpress.org/plugins/index-wp-users-for-speed/

 It works by putting wp_usermeta tags with keys like
 `wp_uifsr:administrator` and `uifsr:subscriber` on each user (it does so
 in a series of wp-cron jobs). It then hooks WP_Query_Users operations and
 replaces the slow and unsargeable

 `meta_key = 'wp_capabilities' and meta_value LIKE '%"administrator"%'`

 query pattern with a much faster

 `meta_key = 'wp_iufsr:administrator'`

 pattern that exploits indexes.  It does the right thing for multisite, for
 example by using `'wp_4_iufsr:administrator'` for site 4.

 It accelerates the Users page and fetching the lists of editors for the
 Posts, Pages, and Gutenberg editor AJAX operation.

 It does not change any existing user CRUD operations, but simply adds this
 extra metadata and hooks queries.

 Please give it a try if you're stymied by a slow dashboard on your site
 with many users.

 As far as fixing this problem in core goes: the root cause of the slowness
 is the difficulty of using MariaDB / MySQL to parse out the role
 meta_values in `wp_capabilities` keys. Giving each role its own meta_key,
 with an empty meta_value, makes the DBMS able to search for roles quickly.

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