[wp-trac] [WordPress Trac] #33885: meta_form performs a potentially expensive query
noreply at wordpress.org
Thu May 25 10:29:33 UTC 2017
#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
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
* 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))
KEY `meta_key` (`meta_key`)
// Because column & key share the same length, the key length definition
* Future versions of MySQL and MariaDB will default to
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
* 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
More information about the wp-trac