Admin: very slow edit page caused by core meta query

Question

Admin: very slow edit page caused by core meta query

psorensen

2022年4月28日 12:38

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.

Topic editor admin Wordpress

Category Web

Nic Bug · Accepted Answer · 2022年4月28日 12:38

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

prosti · Accepted Answer · 2016年9月27日 21:30

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 );

Dan K · Accepted Answer · 2016年7月13日 19:33

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'];
});

birgire · Accepted Answer · 2015年9月23日 17:48

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.

Admin: very slow edit page caused by core meta query

Update:

About