Making user queries in WordPress scale

Jonny Harris avatar
  • As one of the maintainers of the users component in WordPress core, the question of how well sites with lots of registered users scale, is an important one to me. The idea of sites with hundreds of thousands of register user is make more complex when you add multisite into the mix.

    So for a client project an educational site I had built a site that required users to register and login to get access to more lessons. This site became pretty popular, getting over 100,000 registered users in the first 3 months of the site going live. But I noticed one interesting thing, many of the users screens in the CMS, became unusable. This is unfortunately because of how user roles are stored in the WordPress database. User role are stored in user meta, in a serialized array. Meaning to query users by their role (something core does in a number of places), core has to query on the user table, with a join to the user meta that does a like search on an unindexed field (meta value). As you might have guessed, the result of that queries is extremely slow and when you have over 100k users, unless there is massively powerful database server running the query, that query will simply times out.

    So, I had tracked down the badly performing queries, what are the next step to fixing them? Well, first, I needed to review what core functions / classes accessed the users tables and what were the caches were in place. After hours of reviewing core code, I realised one thing. User queries are a mess. Unlike almost all other parts of core, there were so many places in core that were still using raw SQL instead of using the WP_User_Query class. There were many places that should be using caches, but weren’t. There were places that weren’t even using the user cache invalidation function.

    So I set-able fixing a number of these issues. These issues include

    There were others and are I am still working on more. Props to the other members of the core team, specially Adam Silverstein .who worked with me to get all these tickets into WordPress 5.1 and 5.2. But what this means in the end, that is much more constancy on how User data is queried (using WP_User_Query where possible) and how it cached. This by itself it a massive improvement, but I decided push it a little more. I built a feature plugin for core called WP User Query Cache. This plugin use new filtered that were added in 5.1 to cache the result of the query, so that those expensive user queries, need only be run once and the cached. One of the biggest issues, is that sites with lots of users, will likely have those caches invalidated by other users updating their profiles and new users registering. This is not something that is easily solved, however, some level of caching is better than nothing, as it makes the CMS user edit screen accessible again.

    If you are a site that has lots of users and have object caching enabled, please take a look at the WP User Query Cache plugin consider installing and help me test and improve this plugin. Hope to try and push for this to get into core and if it doesn’t this code can always live on as plugin.

    , ,