CUNY Academic Commons

However, I want to know why WordPress put in this restriction in the first place. Boone / Jeremy, is this because the get_users() DB query on 'wp_usermeta' is taxing when there is a large user database? Wouldn't it make more sense for the restriction to count the site's users with count_users() rather than the entire network's user base with wp_is_large_user_count()?

I just read back over the whole ticket history https://core.trac.wordpress.org/ticket/38741 to try to understand the issue. I'm unsure that I have a complete grasp on it :-D But I think the gist is that count_users() itself is very expensive on a large network installation, even if it only returns a small number of users. See https://core.trac.wordpress.org/ticket/38741#comment:31. I guess the risk of your approach is that wp_dropdown_users() will be slow because of how the blog_id param is handled inside of WP_User_Query - ie as a LIKE query against usermeta. As a starting place, I guess I'd recommend some manual testing (perhaps on cdev?) to see whether the proposed patch slows down the loading of edit.php in an appreciable way.

As a starting place, I guess I'd recommend some manual testing (perhaps on cdev?) to see whether the proposed patch slows down the loading of edit.php in an appreciable way.

I actually tried my workaround on cdev first, but cdev has less than 10,000 users, so I tried the workaround on production briefly when I created this ticket and didn't really notice any major slowdown. However, we should err on the side of caution and think of another way to solve this.

I just read back over the whole ticket history https://core.trac.wordpress.org/ticket/38741 to try to understand the issue.

Thanks for linking to that WordPress ticket, Boone. Reading it led me to a few, possible solutions:

1. Perhaps we could populate the Quick Edit author dropdown box with the WP REST API, which is what the Block Editor uses to populate their Author dropdown box. However, by default, the Block Editor limits the author results to 50: https://github.com/WordPress/gutenberg/blob/trunk/packages/editor/src/components/post-author/constants.js. Not sure why it's set to 50.

2. Write our own site user routine to save the site's user IDs into an option and reference these user IDs for the Author dropdown menu with the 'quick_edit_dropdown_authors_args' filter.

3. Use a plugin that attempts to solve the inefficient get_users() 'blog_id' query -- https://wordpress.org/plugins/index-wp-users-for-speed/ :

This plugin adds rows to wp_usermeta describing each user’s role (or roles) in a way that’s easier to search. To find authors, the plugin uses this much faster filter instead.

meta_key = 'wp_index_wp_users_for_speed_role_author'

It takes a while to insert these extra indexing rows into the database; that happens in the background.

Option 1 is the easiest; we'd probably want to remove the pagination restriction though. Option 2 requires doing an initial user ID look up and then listening to whenever a user is added or removed to update the user IDs. I don't really like option 3, but thought I'd mention it.

What do you think, Boone?

Thanks for laying out these options, Ray.

1. Perhaps we could populate the Quick Edit author dropdown box with the WP REST API, which is what the Block Editor uses to populate their Author dropdown box. However, by default, the Block Editor limits the author results to 50: https://github.com/WordPress/gutenberg/blob/trunk/packages/editor/src/components/post-author/constants.js. Not sure why it's set to 50.

The key thing here is not that we are building using the REST API, but that we're building it asynchronously in JavaScript. Isn't that right? Essentially, we'd be accepting the possibility of a slow query, but offloading it onto an async request so that it doesn't affect page load time. IMO this is a pretty good solution, better than adding yet another piece of usermeta that will introduce more bloat and more opportunity for sync issues.

Regarding the pagination, I think that a fully-generalized solution would use a sensible per_page like 50, then make subsequent queries (offset=51, offset=101, etc) until you've got the whole list. You'd then append to the dropdown with each successful query. This is a bit fussy, though, and for the purposes of the Commons we can probably just pick a number that's sufficiently large to handle the vast majority of sites. Something in the 100-200 range is probably ok:

mysql> SELECT meta_key, COUNT(*) as count
    -> FROM wp_usermeta
    -> WHERE meta_key LIKE 'wp_%_capabilities'
    -> GROUP BY meta_key
    -> HAVING count > 100
    -> ORDER BY count ASC;
+-----------------------+-------+
| meta_key              | count |
+-----------------------+-------+
| wp_73_capabilities    |   101 |
| wp_545_capabilities   |   103 |
| wp_1786_capabilities  |   103 |
| wp_731_capabilities   |   109 |
| wp_492_capabilities   |   119 |
| wp_1165_capabilities  |   123 |
| wp_9_capabilities     |   130 |
| wp_26462_capabilities |   130 |
| wp_27028_capabilities |   134 |
| wp_1745_capabilities  |   134 |
| wp_806_capabilities   |   135 |
| wp_1334_capabilities  |   137 |
| wp_3000_capabilities  |   140 |
| wp_13370_capabilities |   140 |
| wp_2025_capabilities  |   142 |
| wp_28721_capabilities |   148 |
| wp_3315_capabilities  |   148 |
| wp_115_capabilities   |   159 |
| wp_25884_capabilities |   162 |
| wp_468_capabilities   |   164 |
| wp_1135_capabilities  |   178 |
| wp_13027_capabilities |   185 |
| wp_2819_capabilities  |   187 |
| wp_977_capabilities   |   192 |
| wp_896_capabilities   |   199 |
| wp_2555_capabilities  |   200 |
| wp_14192_capabilities |   207 |
| wp_6059_capabilities  |   234 |
| wp_1372_capabilities  |   270 |
| wp_2425_capabilities  |   317 |
| wp_877_capabilities   |   400 |
| wp_393_capabilities   |   639 |
| wp_1_capabilities     | 36882 |
+-----------------------+-------+
33 rows in set (0.37 sec)