Swapping a WordPress core meta box to speed up editing

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.

Loading Up the Toolbelt

In our previous article on fixing slow WordPress queries, we reviewed a handful of tools that you can use to identify slow SQL. To work on this particular issue, I used Debug Bar, making sure that SAVEQUERIES was enabled.

Zeroing in on the Slowdown

With SQL queries being tracked, I opened up the Debug Bar interface (using the button in the top right admin bar) and switched to the Queries tab. Here’s what I saw:

Something’s not right here! Any time over 250 milliseconds in the database is alarming; over 1.5 seconds doing SQL queries brings a tear to my eye.

For reference, here’s what the query load looks like on other admin pages:

I scanned the list of queries to see if anything was obviously wrong. I found a suspect:

2,000+ milliseconds for a single query is bad news. Why, WordPress, why?

What is your function, and what does it do?

I needed some context for the query. Pete Sorensen’s tip had given me a good idea where it was coming from, but it’s always good to validate you have the same problem. Time to dig in.

In this case, it’s in the meta_form function, which is located in `wp-admin/includes/template.php`. Right near the top of that function, we see our infamous query:

	$limit = apply_filters( 'postmeta_form_limit', 30 );
	$sql = "SELECT meta_key
		FROM $wpdb->postmeta
		GROUP BY meta_key
		HAVING meta_key NOT LIKE %s
		ORDER BY meta_key
		LIMIT %d";
	$keys = $wpdb->get_col( $wpdb->prepare( $sql, $wpdb->esc_like( '_' ) . '%', $limit ) );

The variable $keys is storing the results of our slow query. Looking further down the meta_form function, we can see where $keys are being used:

foreach ( $keys as $key ) {
	if ( is_protected_meta( $key, 'post' ) || ! current_user_can( 'add_post_meta', $post->ID, $key ) )
		continue;
	echo "\n<option value='" . esc_attr($key) . "'>" . esc_html($key) . "</option>";
}

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:

1. The number of rows (over 1 million)
2. 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:

function admin_speedup_remove_post_meta_box() {
	global $post_type;

	if ( is_admin() && post_type_supports( $post_type, 'custom-fields' ) ) {
		remove_meta_box( 'postcustom', 'post', 'normal' );
		add_meta_box( 'admin-speedup-postcustom', __('Admin Speedup Custom Fields'), 'admin_speedup_post_custom_meta_box', null, 'normal', 'core' );
	}
}

add_action( 'add_meta_boxes', 'admin_speedup_remove_post_meta_box' );

The call to remove_meta_box tells WordPress “Hey, don’t worry about that ‘postcustom’ meta box. We’ll handle it.”

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

1. Not likely to change much
2. 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:

function admin_speedup_post_custom_meta_box( $post ) {
?>
<div id="postcustom">
<div id="postcustomstuff">
<div id="ajax-response"></div>
<?php
$metadata = has_meta($post->ID);
foreach ( $metadata as $key => $value ) {
	if ( is_protected_meta( $metadata[ $key ][ 'meta_key' ], 'post' ) || ! current_user_can( 'edit_post_meta', $post->ID, $metadata[ $key ][ 'meta_key' ] ) )
		unset( $metadata[ $key ] );
}
list_meta( $metadata );
admin_speedup_meta_form( $post ); ?>
<p><?php _e('Custom fields can be used to add extra metadata to a post that you can use in your theme.'); ?></p>
</div>
</div>
<?php
}

Most of the above code is copy + pasted from the WordPress core (see the original code here). The 2 differences in our code:

1. We replaced the call to meta_form with our own function admin_speedup_meta_form, which we’ll define in a moment
2. 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.

Here’s our new code:

function admin_speedup_meta_form( $post = null ) {
	global $wpdb;
	$post = get_post( $post );

	if ( false === ( $keys = get_transient( 'admin_speedup_meta_keys' ) ) ) {
		$limit = apply_filters( 'postmeta_form_limit', 30 );
		$sql = "SELECT meta_key
			FROM $wpdb->postmeta
			GROUP BY meta_key
			HAVING meta_key NOT LIKE %s
			ORDER BY meta_key
			LIMIT %d";
		$keys = $wpdb->get_col( $wpdb->prepare( $sql, $wpdb->esc_like( '_' ) . '%', $limit ) );

		set_transient( 'admin_speedup_meta_keys', $keys, 60 * 60 );
	}

	if ( $keys ) {
		natcasesort( $keys );
		$meta_key_input_id = 'metakeyselect';
	} else {
		$meta_key_input_id = 'metakeyinput';
	}
?>
<p><?php _e( 'Add New Custom Field:' ) ?></p>
<?php // ...snip a bunch of duplicated code... ?>
</tbody>
</table>
<?php

}

The “important changes” are the calls to get_transient and set_transient. If you don’t know what a “transient” is, CSS-Tricks recently published a handy article explaining the nuances of WordPress transients.

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.

— Phil Karlton
source

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:

1. HTML for the button in the meta box
2. 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:

function admin_speedup_post_custom_meta_box($post) {
?>
<div id="postcustom">
<div id="postcustomstuff">
<div id="ajax-response"></div>
<?php
$metadata = has_meta($post->ID);
foreach ( $metadata as $key => $value ) {
	if ( is_protected_meta( $metadata[ $key ][ 'meta_key' ], 'post' ) || ! current_user_can( 'edit_post_meta', $post->ID, $metadata[ $key ][ 'meta_key' ] ) )
		unset( $metadata[ $key ] );
}
list_meta( $metadata );
admin_speedup_meta_form( $post ); ?>
<?php // Here's the new lines: ?>
<?php $current_url = add_query_arg( 'admin_speedup_refresh_meta_keys', '1' ); ?>
<div style="padding: 20px; margin: 20px 0; background: #CCC">
	
</div>
<p><?php _e('Custom fields can be used to add extra metadata to a post that you can use in your theme.'); ?></p>
</div>
</div>
<?php
}

This code adds a new link to our new meta box, which looks like this:

Now, add some code to handle the actual refreshing of meta keys:

function admin_speedup_clear_meta_keys() {
	if ( is_admin() && isset( $_GET['admin_speedup_refresh_meta_keys'] ) && wp_verify_nonce( $_GET['_admin_speedup_nonce'], 'admin_speedup_refresh_meta_keys' ) ) {
		delete_transient( 'admin_speedup_meta_keys' );
	}
}

add_action( 'admin_init', 'admin_speedup_clear_meta_keys' );

Now when the “Refresh Meta Keys” link is clicked, here’s what happens:

1. A nonce is checked to prevent CSRF (more on that here).
2. If the nonce check passes, the transient value is deleted via delete_transient.
3. 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.