Eliminating slow meta queries by repurposing menu_order

Using the WordPress Coding Standards for PHP_CodeSniffer has made me more aware of the meta queries I include in my work. It’s one thing to know meta queries can be very slow, but seeing messages like these below my editor window keeps me mindful of when and how I use them:

Detected usage of meta_key, possible slow query.
Detected usage of meta_value, possible slow query.

On a recent project, the need was particularly acute since the meta_key in question was being used to filter and order around 50,000 posts from a custom post type.

Each post represents a different event, and the events are ordered by date. I first considered eliminating the meta query by storing the event date in one of either the post_date or post_modified columns, but I didn’t want to stop storing timestamps for when the events were created and modified. (And I didn’t want to work around WordPress’s default assumption that future-dated post_date values indicate scheduled posts.)

Since the custom post type doesn’t have page-attributes in its supports array, there’s no way for users to set an event’s menu order, so that seemed like a good place to start in looking for a new place to store the event date. It also felt appropriate to repurpose a database field used for custom ordering, and a Ymd-formatted date (e.g., 20190329) would fit comfortably since the menu_order column’s type is set to INT.

I was already storing the event date as meta data, so I hooked into save_post to copy that field’s value over to menu_order:

/**
 * Populate menu_order with _event_date
 *
 * @param int $post_id The post ID.
 */
function gs_populate_menu_order( $post_id ) {
	// Get the post type.
	$post_type = get_post_type( $post_id );

	// Only populate menu_order for the gs_event custom post type.
	if ( 'gs_event' === $post_type ) {
		// Get the event date from the post meta table.
		$event_date = get_post_meta( $post_id, '_event_date', true );

		// Unhook this function to avoid an infinite loop.
		remove_action( 'save_post', 'gs_populate_menu_order', 20 );

		// Update the post with the menu_order value.
		wp_update_post(
			array(
				'ID'         => $post_id,
				'menu_order' => $event_date,
			)
		);

		// Re-hook this function.
		add_action( 'save_post', 'gs_populate_menu_order', 20 );
	}
}
add_action( 'save_post', 'gs_populate_menu_order', 20 );

With menu_order now populated, we can use WP_Query’s built-in support of ordering by menu_order to sort our posts, and we can filter posts_where if we need to limit our results to specific ranges of menu_order values.

This change dropped the query time from several seconds to fractions of a second, and I have two fewer warnings below my editor window.