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

 Spot on, I can confirm this is fixing critical issues.

 I updated on a client site with 20+ million rows i wp_postmeta and it
 didn't respond at all after update. Just waiting 200+ seconds on each page
 load. After changing the field length to 191 on meta_key its back to it's
 original speed and returning pages in a few milliseconds again.

 As a side note I don't think its good practice to have very longer keys
 for the meta_key anyway so the best solution would probably be to shorten
 the field in a upcoming release.

 It would be nice if we could add som proactive scripts to the WP upgrade
 wizard warning the user about breaking changes before update. This kind of
 info would fit there and I can think up a lot more good cases where both
 plugin and core can detect issue before a core update to avoid downtime.

 Replying to [comment:2 tollmanz]:
 > I think I found the issue with this query. While the index was updated
 to have a length of 191, the field itself was not updated to
 `varchar(191)`. When I change the field to `varchar(191)`, the index works
 on the field and the query is fast (i.e., a few ms).
 >
 > This test run (https://travis-ci.org/tollmanz/utf8mb4-query-
 time/jobs/82861471) shows the results with 1 million rows, using the
 following table:
 >
 > {{{
 > CREATE TABLE `wp_postmeta` (
 >   `meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 >   `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
 >   `meta_key` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 >   `meta_value` longtext COLLATE utf8mb4_unicode_ci,
 >   PRIMARY KEY (`meta_id`),
 >   KEY `post_id` (`post_id`),
 >   KEY `meta_key` (`meta_key`(191))
 > ) ENGINE=InnoDB AUTO_INCREMENT=212732257 DEFAULT CHARSET=utf8mb4
 COLLATE=utf8mb4_unicode_ci;
 > }}}
 >
 > The results are:
 >
 > {{{
 > Query_ID      Duration        Query
 > 1     0.00040875      SELECT DISTINCT meta_key\nFROM wp_postmeta\nWHERE
 meta_key NOT BETWEEN '_' AND '_z'\nHAVING meta_key NOT LIKE '\\_%'\nORDER
 BY meta_key\nLIMIT 30
 > id    select_type     table   type    possible_keys   key     key_len
 ref     rows    Extra
 > 1     SIMPLE  wp_postmeta     range   meta_key        meta_key
 767     NULL    519624  Using where; Using index
 > }}}
 >
 > I think we just need to update the schema to change the field to
 `varchar(191)` and unfortunately write some upgrade routines.

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