[wp-trac] [WordPress Trac] #33885: meta_form performs a potentially expensive query

[WordPress Trac]

#33885: meta_form performs a potentially expensive query
-------------------------------------------------+-------------------------
 Reporter:  jorbin                               |       Owner:  (none)
     Type:  defect (bug)                         |      Status:  new
 Priority:  normal                               |   Milestone:  Future
                                                 |  Release
Component:  Administration                       |     Version:  4.3.1
 Severity:  critical                             |  Resolution:
 Keywords:  has-patch needs-testing needs-unit-  |     Focuses:
  tests                                          |  performance
-------------------------------------------------+-------------------------

Comment (by kraftpixel):

 I'm just sharing observations.

 I just migrated the MySQL server of a website with around 1.4mil postmeta
 rows to MariaDB 10.3 and came across this same issue. Following query took
 30+ seconds to execute.

 {{{
 SELECT DISTINCT meta_key
 FROM #REDACTED#_postmeta
 WHERE meta_key NOT BETWEEN '_'
 AND '_z'
 HAVING meta_key NOT LIKE '\\_%'
 ORDER BY meta_key
 LIMIT 30
 }}}

 EXPLAIN shows the problem as
 {{{
 Using where; Using temporary; Using filesort
 }}}

 After checking max length of existing meta_key(s) and finding it to be
 below 191 characters I altered the table and changed meta_key length to
 191 characters as suggested earlier.
 {{{
 SELECT max(length(meta_key)) FROM #REDACTED#_post_meta
 }}}

 This bought the query time down to 0.021 s

 P.S. Please note, this is an older version of WordPress.

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