[wp-hackers] Optimizing a SQL query used to get all custom post metakeys

[Rarylson Freitas]

Hi,

I am responsible for a Wordpress site with more than 500.000 posts (more
than half million posts).

Recently, I've discovered some performance problems in the function
meta_form, file wp-admin/includes/template.php. This function "Prints the
form in the Custom Fields meta box".

The problem was at line 674, where the SQL query was defined.

I'm putting the raw (already processed) SQL query here:

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

To run this query, MySQL will sort all meta_key lines, including all custom
fields and all the other fields (starting with '_'), unify them, select the
desired fields, and them return them.

The problem with its query is that it will run a sort over all of the
entries (due to the GROUP BY part), and select the desired entries after
the group by (HAVING part). A more efficient solution should consider
excluding unnecessary entries before sorting/unifying them.

A better query could be:

SELECT UNIQUE meta_key
    FROM wp_postmeta
    WHERE meta_key NOT LIKE '\_%'
    ORDER BY meta_key
    LIMIT 30

In my case, this simply change gave us a performance improvement of 4x
(from 8s to less then 2s).

So, I'm proposing this patch to this file, and I hope that it can be useful
to make the Wordpress project better.

--- wp-admin/includes/template.php.orig    2015-07-16 00:22:28.000000000
-0300
+++ wp-admin/includes/template.php    2015-07-16 00:23:06.000000000 -0300
@@ -671,10 +671,9 @@
      * @param int $limit Number of custom fields to retrieve. Default 30.
      */
     $limit = apply_filters( 'postmeta_form_limit', 30 );
-    $sql = "SELECT meta_key
+    $sql = "SELECT UNIQUE meta_key
         FROM $wpdb->postmeta
-        GROUP BY meta_key
-        HAVING meta_key NOT LIKE %s
+        WHERE meta_key NOT LIKE %s
         ORDER BY meta_key
         LIMIT %d";
     $keys = $wpdb->get_col( $wpdb->prepare( $sql, $wpdb->esc_like( '_' ) .
'%', $limit ) );


--

*Rarylson Freitas*Engenheiro de Computação
Instituto Militar de Engenharia