[wp-trac] [WordPress Trac] #64001: Large user data set causes very slow load/pagination/search in the user list

[WordPress Trac]

#64001: Large user data set causes very slow load/pagination/search in the user
list
--------------------------+-----------------------------
 Reporter:  rabbit66      |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Users         |    Version:  6.8.2
 Severity:  normal        |   Keywords:
  Focuses:  performance   |
--------------------------+-----------------------------
 Our WordPress wp_users table has close to 1 million records. When first
 loading the user list we will often get an error 524 timeout.  The
 production environment has older versions of wordpress and MYSQL.

 I used a local test environment, the machine is quite fast, Intel i7, 64
 GB DDR5 RAM, 1TB SSD and an RTX 3060. The OS is Windows 11.

 I have MYSQL 8.0 on the docker host machine and the latest WordPress from
 docker hub running in docker on WSL2. Everything works as expected.

 I loaded 1 million records into the wp_users table and the appropriate
 associated wp_usermeta table, 8999984 records.

 The issue is easy to reproduce. Very poor performance in the user list.

 I loaded the plugin "Query Monitor" to see what was happening.  The result
 was obvious. 2 slow queries:

 The first query took 1.7424 seconds, the query string was:
 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_users.ID
 FROM wp_users
 WHERE 1=1
 ORDER BY user_login ASC
 LIMIT 0, 20
 }}}

 The second query took 32.0632 seconds, the query string was:

 {{{
 SELECT COUNT(NULLIF(`meta_value` LIKE '%\"administrator\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"editor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"author\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"contributor\"%', false)),
 COUNT(NULLIF(`meta_value` LIKE '%\"subscriber\"%', false)),
 COUNT(NULLIF(`meta_value` = 'a:0:{}', false)), COUNT(*)
 FROM wp_usermeta
 INNER JOIN wp_users
 ON user_id = ID
 WHERE meta_key = 'wp_capabilities'
 }}}

 I'm not sure why the user role is stored this way, it seems like a self
 implemented varchar in a longtext (blob) field.  The user role only has 6
 choices, may I suggest that the user role is moved to wp_users and an
 appropriate index created. There are of course many solutions.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/64001>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform