Admin: very slow edit page caused by core meta query

by

We’ve been noticing really long load times when going to edit a post or page. Using Query Monitor, we found that this WP core query is taking upwards to 15-20s.

SELECT meta_key 
FROM wp_postmeta 
GROUP BY meta_key 
HAVING meta_key NOT LIKE '\\_%' 
ORDER BY meta_key 
LIMIT 30

caller: 
meta_form()
post_custom_meta_box()
do_meta_boxes()

We do use a lot of postmeta as one of our post types uses about 20 or so custom fields. I would say maybe we rely too much on postmeta, but this seems like a very inneficient query, seeing that it’s not even selecting the ID of the post.

Is this a common issue? Is there a way to disable this function through a filter? Thanks for any input.

3

If you want to test your custom SQL to see how it affects the loading time, you can try this query swapping: 

/**
 * Restrict the potential slow query in the meta_form() to the current post ID.
 *
 * @see http://wordpress.stackexchange.com/a/187712/26350
 */

add_action( 'add_meta_boxes_post', function( $post )
{
    add_filter( 'query', function( $sql ) use ( $post )
    {
        global $wpdb;
        $find = "SELECT meta_key
                 FROM $wpdb->postmeta
                 GROUP BY meta_key 
                 HAVING meta_key NOT LIKE '\\\_%'
                 ORDER BY meta_key 
                 LIMIT 30";
        if(    preg_replace( '/\s+/', ' ', $sql ) === preg_replace( '/\s+/', ' ', $find )
            && $post instanceof WP_Post  
        ) {
            $post_id = (int) $post->ID;
            $sql  = "SELECT meta_key
                     FROM $wpdb->postmeta
                     WHERE post_id = {$post_id}
                     GROUP BY meta_key
                     HAVING meta_key NOT LIKE '\\\_%'
                     ORDER BY meta_key
                     LIMIT 30";
        }
        return $sql;
    } );                                                            
} );

Here we use the add_meta_boxes_{$post_type} hook, where $post_type="post".

Here we swap the whole query, but we could also have adjusted it to support the dynamic limit.

Hopefully you can adjust this to your needs.

Update:

This potentially slow SQL core query, has now been adjusted in WP version 4.3
from

SELECT meta_key 
FROM wp_postmeta 
GROUP BY meta_key 
HAVING meta_key NOT LIKE '\\_%' 
ORDER BY meta_key 
LIMIT 30

to:

SELECT DISTINCT meta_key
FROM wp_postmeta
WHERE meta_key NOT BETWEEN '_' AND '_z'
HAVING meta_key NOT LIKE '\_%'
ORDER BY meta_key
LIMIT 30;

Check out the core ticket #24498 for more info.

Leave a Comment