[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 OllieJones):

 Coming late to this tix, with some MySQL / MariaDB optimization
 experience.

 **tl;dr: Don't change the MySQL column definitions in core. Address
 performance issues by changing keys (indexes) instead.**

 Here's an explanation of the situation from my perspective. Key/value
 tables like `postmeta` are among the trickiest to optimize in the world of
 SQL. But, of course they allow wide-open extension and creativity. If
 every custom field required a database schema change, well, we probably
 wouldn't have heard of WordPress in 2021. WordPress wouldn't be as wildly
 successful as it is without `postmeta` (and `usermeta`, `termmeta`). So
 our challenge as WordPress.org site operators and developers is to make
 those tables work well at scale. (They already work fine for smaller
 installations.)

 The slow query that's the topic of this tix is

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


 When the table index ("table key" in common WP parlance) is a ''prefix
 index'' such as `wp_postmeta(meta_key(191))` it's useful for satisfying
 WHERE clauses like `WHERE meta_key NOT BETWEEN '_' AND '_z'`: it serves to
 locate the correct rows of the table to use. MySQL uses the index to grab
 the rows and then doublechecks the filter condition against the contents
 of the row.

 But when the index is like `wp_postmeta(meta_key)` (not a prefix index),
 the index *covers* the query
 [https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_covering_index]
 in question.  That is, the query can be satisfied directly from the index.
 That makes this particular query almost miraculously fast. These BTREE
 indexes are already in the right order for `ORDER BY`. `ORDER BY ...
 LIMIT...` is a notorious way to make your MySQL query slow except in the
 cases like this where it can exploit a covering index.

 Here's the thing:  This query is a special case: it only handles one
 column of one table. So, making a covering index just for it then saying
 "wow that's fast! problem solved!"  isn't sufficient justification for a
 core table change.

 I do not believe it's a good idea to change the definition of the table
 from `meta_key VARCHAR(250)` to `191`. Others have pointed out this will
 break some of the zillions of WordPress installations out there, and I
 agree. Plus, the discipline of database administration says "don't change
 the data. change the indexes".

 Good covering indexes often are *compound indexes* indexing multiple
 columns. And they often serve to optimize multiple query patterns, not
 just one.

 Recent MySQL versions (specifically those with the InnoDB Barracuda
 engine) don't have the 767-byte limit on indexes: it's 3072
 [https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html]. Barracuda
 has been available since MySQL 5.7, with some versions of 5.6 also
 supporting it. MariaDB 10.2 and beyond supports Barracuda too.  So the
 prefix indexes aren't necessary any more.

 If any change is necessary in core, it's to the indexes not the tables.
 Rick James and I have been working on a plugin to reindex the `*meta`
 tables and a few others. [https://wordpress.org/plugins/index-wp-mysql-
 for-speed/]. It's still pretty new, but it seems to help. It helps users
 upgrade from MyISAM to InnoDB, then adds some compound indexes. You can
 read about the theory of operation. here.
 [https://www.plumislandmedia.net/wordpress/speeding-up-wordpress-database-
 operations/]

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