[wp-trac] [WordPress Trac] #8561: query in meta_form() function in /wp-admin/includes/template.php causes post/page editing to load slowly

[WordPress Trac]

#8561: query in meta_form() function in /wp-admin/includes/template.php causes
post/page editing to load slowly
----------------------------+-----------------------------------------------
 Reporter:  fastpipe        |       Owner:  anonymous                  
     Type:  defect          |      Status:  new                        
 Priority:  high            |   Milestone:  2.8                        
Component:  Administration  |     Version:  2.7                        
 Severity:  critical        |    Keywords:  meta_key,postmeta,post,page
----------------------------+-----------------------------------------------
 The function meta_form() on line 2329 of /wp-admin/includes/template.php
 contains this query:

 {{{
         $keys = $wpdb->get_col( "
                 SELECT meta_key
                 FROM $wpdb->postmeta
                 WHERE meta_key NOT LIKE '\_%'
                 GROUP BY meta_key
                 ORDER BY meta_id DESC
                 LIMIT $limit" );
 }}}

 On my server, that particular query shows up in the mysql "slow log"
 whenever I go in to edit a post. Presumably, it's being used to populate
 the menu under the "Custom Fields -> Add a new custom field:" section of
 the new/edit post/page form.

 Our wp_postmeta table currently has 428,438 rows so that particular query
 is particularly slow. Unless I'm missing something, a better replacement
 query would be:

 {{{
         $keys = $wpdb->get_col( "
                 SELECT DISTINCT meta_key
                 FROM $wpdb->postmeta
                 WHERE meta_key NOT LIKE '\_%'
                 ORDER BY meta_key ASC
                 LIMIT $limit" );
 }}}

 Running it on my server is more than twice as fast as the original. That
 removes the more resource expensive GROUP BY clause and sorts the result
 by the meta_key value, since using the meta_id for sorting is meaningless
 to populate the menu.

-- 
Ticket URL: <http://trac.wordpress.org/ticket/8561>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software