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.
I just stumbled on this article while researching slow meta queries. This is brilliant, and solved my issue perfectly. Thank you for sharing!
You’re very welcome—I’m glad you found it helpful!