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

 Yes, this solution worked for me. I have a wp_postmeta table with 600M
 rows and was experiencing the exact same slow query (60-80 sec). I used
 this handy command-line utility from the [https://www.percona.com/doc
 /percona-toolkit/2.1/pt-online-schema-change.html Percona Toolkit] to
 modify the '''meta_key''' column from varchar(255) to varchar(191) without
 affecting the current site:
 {{{
 pt-online-schema-change --alter "MODIFY COLUMN meta_key VARCHAR(191)"
 D=mydatabase,t=wp_postmeta
 }}}

 Page load time of the Edit Post screen improved by over 50x.




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