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

[WordPress Trac]

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

Comment (by dbwpe):

 Heya folks,

 I just wanted to chime in real quick to say that @pento's
 [[https://core.trac.wordpress.org/attachment/ticket/24498/24498.patch|24498.patch]]
 was a very significant improvement for one of our customers here with ~3.8
 million rows in _postmeta (InnoDB).

 The single affected query went from ~17 seconds to ~0.03 seconds.

 Original query:

 {{{
 SELECT meta_key
     FROM wp_postmeta
     GROUP BY meta_key
     HAVING meta_key NOT LIKE '\\_%'
     ORDER BY meta_key
     LIMIT 30;
 30 rows in set (16.83 sec)
 }}}

 Initial improvement suggestion:

 {{{
 SELECT DISTINCT meta_key
     FROM wp_postmeta
     WHERE meta_key NOT LIKE '\_%'
     ORDER BY meta_key
     LIMIT 30;
 30 rows in set (4.03 sec)
 }}}

 @pento version

 {{{
 SELECT DISTINCT meta_key FROM wp_postmeta
     WHERE meta_key NOT BETWEEN '_' AND '_z'
         AND meta_key NOT LIKE '\_%'
     ORDER BY meta_key
     LIMIT 30;
 30 rows in set (0.03 sec)
 }}}

 It's worth noting that on this site, most of this site's _postmeta are
 from WooCommerce, which mostly begin with underscores, so excluding those
 really has a big impact.

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