Admin: very slow edit page caused by core meta query

by

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