[wp-trac] [WordPress Trac] #24498: Improving performance of meta_form()

[WordPress Trac]

#24498: Improving performance of meta_form()
----------------------------+------------------------------
 Reporter:  lumaraf         |       Owner:
     Type:  enhancement     |      Status:  new
 Priority:  normal          |   Milestone:  Awaiting Review
Component:  Administration  |     Version:  3.5
 Severity:  normal          |  Resolution:
 Keywords:  has-patch       |     Focuses:  performance
----------------------------+------------------------------

Comment (by rarylson):

 Hi,

 Recently, I send an email to the Wordpress dev list and Chris Christoff
 told me about this thread.

 I'm copy/pasting the most important part of my email here:

 ---

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

 ----

 I'm happy that @pross (comment 10) just proposed the same patch than me.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/24498#comment:11>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform