Large user data set causes very slow load/pagination/search in the user list

[rabbit66]

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.

[westonruter]

Here's where that especially slow query is being constructed: ​https://github.com/WordPress/wordpress-develop/blob/6df41d82afb2fc1eddfb8aada9918cdad264283c/src/wp-includes/user.php#L1360-L1385

[westonruter]

The code in question was introduced 15 years ago in WP 3.0 via r13576 (​1604a2e) to fix #11914.

[westonruter]

Adding a multisite focus, as this is likely going to be an issue on large networks as well.

[spacedmonkey]

There are already two tickets to would be fix this issue currently working on this release. #63021 #58001.

It is also strongly recommended for a site of this site, to use object caching, especially if it is multisite.

There is also pre_count_users filter, that could be used here, to add custom caching or to bypass this logic all together.