[wp-trac] [WordPress Trac] #24498: Improving performance of meta_form()

[WordPress Trac]

#24498: Improving performance of meta_form()
----------------------------+--------------------------
 Reporter:  lumaraf         |       Owner:  pento
     Type:  enhancement     |      Status:  closed
 Priority:  normal          |   Milestone:  4.3
Component:  Administration  |     Version:  3.5
 Severity:  normal          |  Resolution:  fixed
 Keywords:  has-patch       |     Focuses:  performance
----------------------------+--------------------------

Comment (by tollmanz):

 I ran into this problem recently. When testing an update of a WP site with
 ~4 million post meta rows, I began seeing issues with this query. Before I
 knew this ticket existed, I worked on a reduced test case to illustrate
 the problem. Fortunately, I think it'll be really helpful for testing new
 queries as we move along.

 I created a script and Travis CI integration
 (https://github.com/tollmanz/utf8mb4-query-time) that generates test data
 and a number of scenarios to test these queries. The Travis CI integration
 does the following:

 * Creates a SQL file with 1 million insert statements to populate a
 `wp_postmeta` table
 * Creates a 4.1.x style `wp_postmeta` table without the `utf8mb4`
 character set/collation and populates it with the test data
  * Runs the 4.1.x style query
  * Runs the 4.3.x style query
 * Creates a 4.3.x style `wp_postmeta` table with the `utf8mb4` character
 set/collation and populates it with the test data
  * Runs the 4.1.x style query
  * Runs the 4.3.x style query
 * Creates a 4.3.x style `wp_postmeta` table with the `utf8mb4` character
 set/collation; however, switches the `meta_key` column back to `utf8`.
 This helps isolate the character set as the concern (and additionally
 allows me to test a temporary workaround that I might use). The test data
 is populated into this table.
  * Runs the 4.1.x style query
  * Runs the 4.3.x style query

 To be clear, the "4.1.x style query" is:

 {{{
 SELECT meta_key
 FROM wp_postmeta
 GROUP BY meta_key
 HAVING meta_key NOT LIKE '\_%'
 ORDER BY meta_key
 LIMIT 30;
 }}}

 And the "4.3.x style query" is:

 {{{
 SELECT DISTINCT meta_key
 FROM wp_postmeta
 WHERE meta_key NOT BETWEEN '_' AND '_z'
 HAVING meta_key NOT LIKE '\_%'
 ORDER BY meta_key
 LIMIT 30;
 }}}

 If you view the Travis build, you'll be able to see query results, timing
 information, and `EXPLAIN` information.

 The TL;DR results of a recent run (https://travis-ci.org/tollmanz/utf8mb4
 -query-time/builds/80031807) were:

 {{{
             | utf8        | utf8mb4      | utf8 meta_key |
             |-------------|--------------|---------------|
 4.1.x query | 0.00036725s | 21.15712650s | 0.00038975s   |
 4.3.x query | 0.00051800s | 21.58118725s | 0.00043800s   |
 }}}

 If anyone would like me to add more test queries or change things up here,
 I'm more than happy to do so. This is really easy now that it's scripted.
 Additionally, you could just fork it, make changes, and PR, which will
 kick off a run.

 Is there any reason that the `meta_key` column has to be utf8mb4? Would it
 make sense to consider reverting it to its previous state? That feels
 kinda dirty to me, but it is at least worth consideration.

 Note...I tried to run @dd32's query in these tests, but it's not set up to
 have a `wp_posts` table yet, so I'd need to do some more work on that. I
 did, however, try the query against the DB that started me looking at this
 and the query was taking ~15s. The post's table is in the neighborhood of
 500k rows.

 h/t to @jorbin for working through parts of this with me.

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