[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:  Awaiting Review
Component:  Administration  |     Version:
 Severity:  normal          |  Resolution:
 Keywords:                  |     Focuses:  performance
----------------------------+------------------------------

Comment (by 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:2>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform