Opened 11 years ago
Last modified 3 years ago
#28160 new enhancement
Get authors user query in-efficient when dealing with large numbers of users.
Reported by: | l3rady | Owned by: | |
---|---|---|---|
Milestone: | Awaiting Review | Priority: | normal |
Severity: | major | Version: | 3.9 |
Component: | Users | Keywords: | |
Focuses: | Cc: |
Description
When in WordPress admin the following query is run:
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 CAST(wp_usermeta.meta_value AS CHAR) != '0' ) ) ORDER BY display_name ASC;
This is getting a list of authors for the current site. Now this query is fine for small sites but is incredibly slow when dealing with large WP installations with thousands of users.
For example one of our largest WP installations has over 225K Users with over 7M usermeta records and the above query takes, on our server, over 34 seconds to complete. This is long enough for connections to timeout and the resulting data to not be cached and run over and over until the database queue is so long that PHP starts to crash.
Now the above query is generated deep within the WP_User_Query class and understand that the query is generated in such a way that allows a number of queries to be built dynamically, but the above query is very in-efficient. I've re written the query and my new query only takes 700 milliseconds to run.
SELECT wp_users.ID, wp_users.user_login, wp_users.display_name FROM wp_usermeta LEFT JOIN wp_users ON (wp_users.ID = wp_usermeta.user_id) WHERE wp_usermeta.meta_key = 'wp_user_level' AND wp_usermeta.meta_value != '0' HAVING wp_users.ID IS NOT NULL ORDER BY display_name ASC;
I'm looking into how I can improve the queries in the WP_User_Query class but thought it would be good to bring this to the attention of some core devs.
Change History (15)
#2
@
10 years ago
@l3rady: I tried your patch, but still face problems.
Got >10k users (subscribers) and when I open a post it will take about 30 sec before the page loads.
Have you encountered the same problem? Any ideas how to resolve it? :)
This ticket was mentioned in Slack in #core by boone. View the logs.
10 years ago
#4
@
9 years ago
Hey all, I'm working on a client site with hundreds of thousands of users, and I've come across this slow query as a result. In my particular instance, it appears that WP_User_Query->query() gets called two times by the WordPress Core, and due to the number of users, it takes ~50-60 seconds to process each query (so, two minutes to fully load an edit page).
It looks like this has been in Trac for awhile, and the logs show some related issues. Any chance we can get some focus on this for 4.4?
This ticket was mentioned in Slack in #core by jmichaelward. View the logs.
9 years ago
#6
@
9 years ago
- Severity changed from normal to major
I'd like to +1 this. We're seeing this query take 10-25 seconds. So just to load the list of posts we have to wait forever. We require registration to comment, so we have lots of non-admin users (243,000+) and the wp-admin area is nearly impossible to use.
#7
follow-up:
↓ 9
@
8 years ago
I made my first WP Plugin to hack around this. It basically just creates/maintains a cache table of author user_ids.
#9
in reply to:
↑ 7
@
7 years ago
Replying to brandonliles:
I made my first WP Plugin to hack around this. It basically just creates/maintains a cache table of author user_ids.
Thank you for creating and sharing this.
Question: will this remove deleted users from cache table?
Not seeing it in the code but perhaps I'm missing it.
Thinking something like this would to the trick...
add_action('deleted_user', function($id, $reassign) {
$a10x = new Admin10X();
$a10x->del_author($id);
}, 10, 2);
#10
follow-up:
↓ 11
@
7 years ago
Thanks for the suggestion @toddlevy ! Do you want to open a pull request on the github project? I'd be happy to review it and update the plugin.
#11
in reply to:
↑ 10
@
7 years ago
Replying to brandonliles:
Thanks for the suggestion @toddlevy ! Do you want to open a pull request on the github project? I'd be happy to review it and update the plugin.
Found another smallish issue... since the Admin10X class doesn't get instantiated until pre_user_query
or set_user_role
gets called, the table never gets created and populated.
Don't have a "real" solve but I was able to work around it by sticking this at the top of the plugin when I activated... $a10x = new Admin10X();
... and then commenting it out.
#12
@
7 years ago
Thanks again @toddlevy! I've fixed the plugin installation/uninstallation issue, incorporated your changes and added you as a contributor!
#13
@
5 years ago
Can you please include this in WP Core? It took me ages to debug on our site with 400k users and @brandonliles plugin is the only solution that worked. This would really prevent future users to loose lots of time profiling the issue. Thank you.
#14
@
3 years ago
Rick James and I have released a plugin to change the indexes on wp_usermeta (and on other tables) to give a significant performance boost to the kind of query in this ticket.
Here's the plugin. https://wordpress.org/plugins/index-wp-mysql-for-speed/ Nothing would make us happier than for our plugin to be rendered obsolete by improvements in core or elsewhere.
Here's something I wrote for a related ticket. https://core.trac.wordpress.org/ticket/33885#comment:86
@SergeyBiryukov says they are looking at this indexing opportunity in the performance team. https://github.com/WordPress/performance/issues/132
All that being said, this WHERE clause CAST(wp_usermeta.meta_value AS CHAR) != '0'
could be simpler, slightly faster, and still precisely equivalent, if it said simply wp_usermeta.meta_value != '0'
.
#15
@
3 years ago
I've just released another database speedup plugin. This one mitigates MySQL inefficiencies when handling tens of thousands of users (or more). Some may find it useful. https://wordpress.org/plugins/index-wp-users-for-speed/
I have a quick fix for the this but is incredibly hacky: