10

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.

  • Does this happen without any plugins and the default theme? – birgire May 7 '15 at 17:04
  • Yes it does. As mentioned above, I've identified the slow query as belonging to WP core. With the function in the answer I've provided, the custom fields meta box is disabled, which prevents the query from running. – psorensen May 7 '15 at 22:32
  • 2
    I see it know, I just checked out the meta_form() function and this is indeed the generated SQL query from that core function. You could try to add your own custom metabox with modifications to the code in meta_form() and use there your suggested SQL query. I found this #8561 closed trac ticket. You could perhaps create another ticket or try to reopen this one? PS: Notice that the parent page selecting metabox is also problematic. If you got 1 million pages, then all of them will show up as select options! – birgire May 8 '15 at 1:34
  • 2
    A solution proposed on CSS-Tricks: css-tricks.com/… – psorensen Sep 23 '15 at 17:25
  • Interesting solution there, but it looks like it's replacing the whole meta_form() function. I updated the answer - the core SQL query has been adjusted in WP version 4.3.. Do you see any performance gain with this new SQL query compared to our additional post_id restriction ? – birgire Sep 23 '15 at 18:08
5

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.

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'];
});
  • Interesting. Where in the source code does this exist? – psorensen Jul 14 '16 at 0:38
  • wp-admin/includes/template.php:595 as of 4.4 – markdwhite Nov 8 '16 at 3:59
1

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

Removing the metaboxes also prevents the slow query.

function remove_metaboxes() {
     remove_meta_box( 'postcustom', 'page', 'normal' );
}
add_action('admin_menu', 'remove_metaboxes');

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.