With our latest version 27.8 we have introduced performance optimizations designed to reduce loading times for all plugin features. This is particularly noticeable on large websites with many posts and users.
Note: This post contains technical content and implementation details.
Offering well-tuned software with minimal server overhead and fast loading times is always at the forefront of everything Yoast developers do. However, Yoast SEO is installed on millions of websites, so the variety of setups we need to be well prepared for is wide. This means that we should continuously scan for windows to optimize the performance of the plugin. We are known to have done this consistently in the past, for example when we improved our database system.
Version 27.8 is the result of one of these targeted reviews. We deliberately selected features whose behavior had the most flexibility at scale and reengineered them to be leaner and faster. From changing queries to make pages faster for sites with lots of users and reducing heavy administrative operations for sites with lots of posts, to reducing round trips into the database for multiple functions and generally applying performance best practices, this is a release that aims to improve the user and developer experience in the Yoast SEO plugin.
We’d also like to provide a technical summary of the improvements in this release, focusing on the nitty-gritty details, because it’s always nice to raise awareness of performance best practices (not to mention, it’s always fun to talk about code).
Significantly reduce root sitemap load times on sites with many users
For context, in order for Yoast SEO to calculate the Last Modified value of the Author Sitemap, it uses the user meta of all users authorized to be included in the Author Sitemap when outputting the root sitemap.
Calculating authorized users has traditionally been done by verifying user capabilities. This was achieved by adding the argument “capability” => (“edit_posts”) in the get_users() call used. This triggered a very large query with multiple joins and without using the database’s indexes.
Specifically, the resulting query added a clause like this:
AND ((((mt1.meta_key = 'wp_capabilities'
AND mt1.meta_value LIKE '%\"edit\\_posts\"%')
OR (mt1.meta_key = 'wp_capabilities'
AND mt1.meta_value LIKE '%\"administrator\"%')
OR (mt1.meta_key = 'wp_capabilities'
AND mt1.meta_value LIKE '%\"editor\"%')
OR (mt1.meta_key = 'wp_capabilities'
AND mt1.meta_value LIKE '%\"author\"%')
OR (mt1.meta_key = 'wp_capabilities'
AND mt1.meta_value LIKE '%\"contributor\"%')
OR (mt1.meta_key = 'wp_capabilities'
AND mt1.meta_value LIKE '%\"wpseo\\_manager\"%')
OR (mt1.meta_key = 'wp_capabilities'
AND mt1.meta_value LIKE '%\"wpseo\\_editor\"%'))))Since HOW ‘%…%’ Since a B-tree index cannot be used, MySQL must fully read each matching wp_capabilities row and perform seven substring scans of the PHP serialized meta value per row.
By changing this calculation from using the skill check to searching for users with published posts (via using the has_published_posts => true argument), we immediately transformed the resulting query into one that uses indexes and performs much better on sites with many users.
In fact, one of our tests on a site with about 2 million users measured the time it took to complete each query (i.e., roughly the time it took to render the root sitemap). from over 300 seconds to just 25 milliseconds! This means the change has the potential to dramatically improve root sitemap load times on similar websites.
Finally, considering that the argument “has_published_posts” => true was already used in a later phase of sitemap generation, the change itself should have little to no negative impact on the actual functionality of the feature.
Reduce author sitemap load times on sites with many users
In order for Yoast SEO to render author sitemaps, the authorized users must be calculated. On websites with many users, this can be a very laborious process. Apart from the above optimization, we noticed that although Yoast SEO calculated legitimate users, it also added a meta query to check if the User level of each user was above 0.
It turned out that this was a relic from ancient times, because The user_level framework has been deprecated since version 3.0 of WP Core. While this didn’t cause any problems with our sitemap functionality, it did unnecessarily insert an INNER JOIN into the resulting query without much purpose and degraded performance for sites with very large user and usermeta tables. So we removed the unnecessary JOIN:
INNER JOIN wp_usermeta AS mt1 ON wp_users.ID = mt1.user_id
...
AND ( mt1.meta_key = 'wp_user_level' AND mt1.meta_value != '0' )Since the user_level framework was deprecated a long time ago, we made a conscious decision to stop supporting it, especially because it would make our functionality smoother. In fact, we are happy with the delivery of this optimization and expect it to cause minimal disruption, especially given the age of this setting.
Avoid unnecessary expensive database queries on admin pages
In order to notify administrators in a timely manner that they need to take the necessary actions to ensure that their website data is optimally indexed in our internal storage, Yoast SEO used to run a daily database query while administrators navigated through the backend. On large websites, this database query could run for several seconds, which regularly slowed down the rendering of admin pages.
In particular, the Limited_Indexing_Action_Interface::get_limited_unindexed_count() function, which can perform complex queries like below, was regularly running on admin pages, slowing down the speed of rendering larger websites.
SELECT Count(P.id)
FROM wp_posts AS P
WHERE P.post_type IN ( 'post', 'page' )
AND P.post_status NOT IN ( 'auto-draft' )
AND P.id NOT IN (SELECT I.object_id
FROM wp_yoast_indexable AS I
WHERE I.object_type = 'post'
AND I.version = 2)We managed to redesign the logic of the code responsible for the notification that informs administrators of pending actions so that these large queries are now only executed once, at the moment when it is first detected that such a notification should be created.
In this way, we are effectively caching the results of Limited_Indexing_Action_Interface::get_limited_unindexed_count() and relying on the cache invalidation that existed before our changes but was not used properly. As a result, a potentially very large database query was no longer triggered just once every day (and every 15 minutes on very busy sites with many concurrent users) on most sites.
Optimize expensive database queries on admin pages
In conjunction with the query prevention change mentioned above, we not only managed to avoid running the above-mentioned large database query more than once per site, but we also managed to optimize the query itself. An additional benefit of this is that we have made the SEO optimization tool much faster on sites with a lot of posts.
Specifically, we assumed the following:
AND P.ID NOT IN (
SELECT I.object_id FROM wp_yoast_indexable AS I
WHERE I.object_type = 'post'
)To:
AND NOT EXISTS (
SELECT 1 FROM wp_yoast_indexable AS I
WHERE I.object_id = P.ID
AND I.object_type = 'post'
)Because NOT IN (subquery) creates the entire list of object IDs while the second query short-circuits the moment a row matches, the query runs significantly faster on sites with several thousand posts.
Reduce round trips to the database
As a rule of thumb, round trips to the database are costly operations that should be kept to a minimum whenever possible. Our reviews discovered cases where we retrieved data for multiple posts in consecutive SELECT queries, when we could have run a single batch SELECT query to collect data for all posts at once.
For example, code that looked like this:
$indexables = ();
foreach ( $post_ids as $post_id ) {
$indexables() = $this->repository->find_by_id_and_type( (int) $post_id, 'post' );
}was refactored into something that looked like this:
$ indexables = $this->repository->find_by_multiple_ids_and_type(
\array_map( 'intval', $post_ids ),
'post',
);This meant that for a block of 1,000 posts, instead of doing 1,000 SELECT queries that returned a maximum of one row, we now perform a single SELECT query that returned a maximum of 1,000 rows. Of course, we made sure that the contributions requested each time do not exceed a certain threshold to avoid reaching MySQL usage limits.
This would make websites with e.g. For example, 1000 posts can save 960 round trips to the database for certain operations, such as part of their SEO optimization or part of the output of the schema aggregation function.
Improve post editor performance by preventing unnecessary re-rendering
The WordPress editor re-renders Yoast’s sidebars when the data retrieved from the store appears to have changed. Unfortunately, “seems to have changed” is decided by reference equality (JavaScript ===) and not by value comparison. A selector that returns { items: (‘foo’) } looks just like a human, but if it’s a new object literal every time, React will treat it as new and re-render the panel. And if we multiply that by a busy editor that triggers status updates with every keypress, we get panels that are constantly re-rendering for no reason.
With version 27.8, we identified several cases where data that wasn’t actually changed was triggering unnecessary re-renders in the post editor and patched these, making our editor integration much more robust and performant.