Admin: very slow edit page caused by core meta query

Question

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.

Answer 1

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.

Answer 2

If you browse through the source code of the function you'll find this:

$keys = apply_filters( 'postmeta_form_keys', null, $post );
if ( null === $keys ) {
    ...      
}

Using the postmeta_form_keys hook you can manually specify the keys to avoid calling this inefficient query altogether:

add_filter('postmeta_form_keys', function(){
    return ['your_meta_key'];
});

Answer 3

Can you try this out. This is not a solution, but a temporary workaround.

// disable big slowdown http://wordpress.stackexchange.com/questions/187612/admin-very-slow-edit-page-caused-by-core-meta-query
function dj_limit_postmeta( $string, $post ) {
    return array(null);
}
add_filter( 'postmeta_form_keys', 'dj_limit_postmeta', 10, 3 );

Answer 4

had that issue still in the year 2022 because i have over 10.000 posts and each has 30 meta keys/values. so there is an easy and performant workaround if you insert that piece of code in your functions.php:

function set_postmeta_choice( $string, $post ) {
    $meta_keys = array();
    foreach(has_meta( $post->ID ) as $meta){
        $meta_keys[] = $meta["meta_key"];
    }
    return $meta_keys;
}
add_filter( 'postmeta_form_keys', 'set_postmeta_choice', 10, 3 );

this way you still have the meta_key suggestions in the dropdown field

Answer 5

If you need custom fields in your post/order edit page then change the query in wp-admin/includes/template.php FROM:

SELECT DISTINCT meta_key
            FROM $wpdb->postmeta
            WHERE meta_key NOT BETWEEN '_' AND '_z'
            HAVING meta_key NOT LIKE %s
            ORDER BY meta_key
            LIMIT %d

TO:

SELECT DISTINCT meta_key
            FROM wp_postmeta
            WHERE meta_key NOT LIKE '\_%'
            ORDER BY meta_key
            LIMIT 70

Check your post edit page to see if you are able to find the custom field you need, and adjust the LIMIT value according. Lesser the limit, the faster will be your page.

If you do not need the custom field dropdown at all then replace the code with:

SELECT DISTINCT meta_key
            FROM wp_postmeta
            WHERE meta_key NOT LIKE '\_%'
            LIMIT 1

This should speed up your backend pages.