[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:
     Type:  defect (bug)             |      Status:  new
 Priority:  normal                   |   Milestone:  Future Release
Component:  Administration           |     Version:  4.3.1
 Severity:  critical                 |  Resolution:
 Keywords:  has-patch needs-testing  |     Focuses:  performance
-------------------------------------+-----------------------------

Comment (by johnjamesjacoby):

 Looking at this more, we are lucky that ordering by `meta_key` is not very
 popular. This appears to be the only place in core where that happens, and
 none of the more popular plugins have do this either.

 In addition, `WP_Meta_Query` does not support ordering by `meta_key` – any
 attempts to do so are ignored.

 ----

 The simplest option is @pento's original patch. Removing the `ORDER BY`
 clause (or using `ORDER BY NULL`) prevents MySQL from `Using filesort` on
 the result set, which does result in an overall performance improvement.

 Unfortunately, it isn't until the `VARCHAR` length matches the `meta_key`
 index length that `Using temporary` stops and the `meta_key` index is
 fully utilized.

 ----

 As far as directly altering `_postmeta` in concerned:

 {{{
 ALTER TABLE wp_postmeta MODIFY meta_key varchar(191);
 }}}

 I'd like to caution against doing this for a few reasons:

 * Doing this only to `_postmeta` and not the other meta-data tables goes
 against the normalization of the meta API. It's a change you will quickly
 forget you made here, and will inevitably cause a problem years from now
 in your application.
 * You'll want to run `ANALYZE TABLE wp_postmeta;` afterwards, or visit
 `/wp-admin/maint/repair.php` and follow the on-screen instructions. This
 is because `ALTER`ing the database table alone does not rebuild the key
 distributions.
 * On tables with millions of rows, both the `ALTER` and the above `ANALYZE
 TABLE` queries will take several seconds (or minutes) to complete.
 * On installations with replication, you will want to monitor your
 secondaries, making sure their key distributions match the primary.
 * Running this `ALTER` has the hidden consequence of also updating the
 `meta_key` index to assume the length of the `meta_key` column, so future
 `ALTER`s on this column will directly effect the index too.
 {{{
 KEY `meta_key` (`meta_key`(191))
 // Becomes:
 KEY `meta_key` (`meta_key`)
 // Because column & key share the same length, the key length definition
 is dropped
 }}}

 ----

 My conclusions:

 * Future versions of MySQL and MariaDB will default to
 `innodb_large_prefix` [https://mariadb.com/kb/en/mariadb/xtradbinnodb-
 server-system-variables/#innodb_large_prefix being `ON` by default]. This
 won't directly avoid temporary tables & filesorts, but it will generally
 improve query performance the way @matt_fw eluded to earlier.
 * For anyone that has added an index to their `meta_value` columns (not
 uncommon for sites that do complex `JOIN`s), you'll want to keep a similar
 eye on your customizations.
 * Because of how rare & weird it is to `ORDER BY meta_key`, I do think
 it's OK to not optimize the meta database tables for his behavior.
 * I do not think it's OK for our meta database tables to all have an
 ineffective `meta_key` index. Thankfully, even when their lengths are
 misaligned, the `meta_key` index does still function as intended for all
 queries other than `ORDER BY meta_key`.
 * @dd32's transient approach in
 [https://core.trac.wordpress.org/ticket/33885#comment:44 #44] is a sound
 work-around for this isolated problem, for now.
 * For later, we should consider what a redux of this meta-box experience
 looks like.
 * Like we have `wp_is_large_network()`, we could consider a
 `wp_is_large_site()` companion. In theory, it could be used by plugins
 like WooCommerce or bbPress to announce to the rest of the environment
 "hey, maybe the posts table will have more rows than is considered
 typical" so other aspects of the system can respond accordingly.

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