Swapping a WordPress core meta box to speed up editing

Share this:

The CSS-Tricks front end is usually pretty darn fast, because most pages are cached (and don't need to be dynamically generated when requested). However, up until recently, the CSS-Tricks WordPress admin didn't have the same luck.

In particular, the post editing screen was slow. Painfully slow. Saving a draft took several seconds, which is enough to break your flow in the middle of writing an article.

Working on a tip from Pete Sorensen, who posted a performance question on the WordPress StackExchange (and was kind enough to tell us about it), I set out to figure out what was making the post editing page so slow.

Those keys are meta_keys in the wp_postmeta table, and they're being used to populate options for a <select> in the "Custom Fields" meta box on the edit post screen.

Why Is It So Slow?

To get a better idea of why this query is slow, I used phpMyAdmin to run an EXPLAIN query for the slow SQL. EXPLAIN gives us insights into how MySQL executes a query. For complicated queries, EXPLAIN can help you pinpoint slow points in your SQL - sometimes it's a slow subquery or inefficient operation that borks your query.

Even the case of simple queries like our meta SQL above, EXPLAIN still gives us a smidgen of information to help us understand what is going on.

The important pieces to look at for this query are:

The number of rows (over 1 million)

The "Extra" column, which gives us the ambiguous phrase "Using filesort"

Using filesort (#2) is actually a big deal, because it means that every time this query is run the rows are being sorted, which is an expensive operation.

So what can we do about it? Thanks to WordPress hooks, we can replace the slow code with some of our own!

Replacing the Meta Box

Any time you're making a change to the WordPress core, you should look to see if there are any actions or filters you can hook into to make your changes. Hooks are a non-intrusive way to make changes to code that doesn't belong to you.

Unfortunately, you won't always find a hook to do what you're looking for - particularly in older parts of WordPress. Searching through `wp-admin/includes/meta-boxes.php` and `wp-admin/includes/template.php` (where our slow code lives) turns up no actions or filters for us to hook into.

When we can't hook into existing meta box code, we still have an option: We can replace the meta box with one of our own.

The first step is to remove the core meta box and tell WordPress about our new meta box. We can do so using the add_meta_boxes hook:

With the core meta box removed, the call to add_meta_box tells WordPress to use the admin_speedup_post_custom_meta_box function to generate the HTML for our new meta box.

admin_speedup_post_custom_meta_box is basically a copy of the post_custom_meta_box function from WordPress core with a few changes; most notably, we replace the call to meta_form with our own custom function, admin_speedup_meta_form.

Big copy + paste warning: Replacing blocks of code with nearly identical code isn't ideal; if something changes in the original code, we have to be sure to update our version of that same code.

But in this case, the Custom Fields meta box is

Not likely to change much

Not a critical feature - even if it broke for a bit when updating WordPress, the site wouldn't go down

Given the minimal risks involved, and given that we can remove over 2 seconds from a page load, I judged it reasonable to swap out the core meta box.

Writing the New Meta Box HTML

Now that we've convinced ourselves of the moral argument for replacing a core meta box, we can write the functions that display our mutant version of the Custom Fields box.

First, we'll define admin_speedup_post_custom_meta_box, which is the function WordPress will use to write the HTML for our new meta box:

We replaced the call to meta_form with our own function admin_speedup_meta_form, which we'll define in a moment

We added an extra wrapper div with ID postcustom - this ID is expected by some JavaScript that gives the meta box AJAX functionality, and it's a lot easier to keep things as close as possible to the original markup, lest we break something

Fixing the Slow Query

Finally, we're at the point where we can fix this slow bugger. The SQL for our meta_key SQL is located in the meta_form function in `wp-admin/includes/template.php`. We'll solve this inside our admin_speedup_meta_form function, which is basically the same thing as the original meta_form function with one important change.

For the purposes of this article, transients are simply a way to store the results of an expensive bit of code, so they don't have to be regenerated later. In this case, we're storing the results of an expensive SQL query using the transient key admin_speedup_meta_keys. We first check to see if WordPress has already stored the value for us, using get_transient. If so, we can skip the SQL query and continue as normal. If not, we do the slow SQL query and store the results for an hour (60 * 60 seconds).

With the transients code in place, our slow SQL query will only be run approximately once per hour - much better than on every request!

With our code in place, query monitor shows us the slow query isn't happening on every page load:

Success! Only one issue remains: Suppose someone added a new meta_key to the list. Our list, stored as a transient, would be invalid for approximately 1 hour. We need a way to bust the cached query results.

Adding the "clear meta keys" button

There are only two hard things in Computer Science: cache invalidation and naming things.

Knowing when a cached value is invalid is a hard problem. There are lots ways the list of meta_keys can be changed or added, including plugins automatically adding keys or admin users manually entering them in.

Whenever I hit a hard problem, I try to do the laziest thing possible: Leave it alone.

Meta boxes are only used in the WP admin, and this particular query result won't change often. So in this case, I decided to just add a button to clear the cached (transient) value, so an admin user could simply regenerate the list of meta_keys if they noticed it was out of date. It's not elegant, but it works and it's simpler than trying to invalidate the cache.

To add the button, we need 2 bits of code:

HTML for the button in the meta box

Code on the backend to clear the transient value

First, I added the button to the newly-minted admin_speedup_post_custom_meta_box function:

If the nonce check passes, the transient value is deleted via delete_transient.

After the transient is deleted, the (slow) SQL query will be redone and the list of meta_keys will be current

Recapping What We Did

We found a slow bit of SQL that was living on one of the core WordPress meta boxes. After mulling our options for fixing slow SQL, we decided to simply cache the results of the query to keep things simple.

To do that, we had to unhook and replace the core meta box with a nearly-identical custom meta box that cached the results of the slow query. Finally, to prevent the cached results from getting stale, we added a way for admin users to clear the cache.

The results: 1.5 seconds shaved off the typical Edit Post page load, which is a noticeable improvement.

It’s no wonder that query is slow. It does a GROUP BY / HAVING without any need for the GROUP BY. Presumably this is done to get distinct values. They do it in pretty much the slowest way possible though.

SELECT DISTINCT meta_key
FROM $wpdb->postmeta
WHERE meta_key NOT LIKE %s
ORDER BY meta_key
LIMIT %d

Does exactly the same and will be much easier on the database. It’s also a much easier fix than having to do caching and cache invalidation etc.

Using that, you can tell the transient to update in the background. That way, when it is time for the transient to update, you don’t have to wait however long that takes. It will show the current transient and on next page load it will show the new one (without a long load time).

👋

CSS-Tricks* is created, written by, and maintained by Chris Coyier and a team of swell people. It is built on WordPress and powered up by Jetpack. It is made possible through sponsorships from products and services we like.