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

 I've been following this ticket and have done some testing on a dataset
 from a large WordPress install that is currently unusable on 4.3 because
 of this bug.  I can confirm that changing the field to VARCHAR(191) does
 result in the query using the index and brings performance to near 4.2
 levels.

 Here are the results of my testing:

 CentOS 7
 MariaDB 5.5.40

 postmeta table has ~3.4 million rows and is 1.4 GB in size

 4.1 query, UTF8mb4 meta_key:
 ''SELECT meta_key
 FROM wp_5_postmeta
 GROUP BY meta_key
 HAVING meta_key NOT LIKE '\_%'
 ORDER BY meta_key
 LIMIT 30;''
 '''14.47 seconds'''

 4.3 query, UTF8mb4 meta_key:
 ''SELECT DISTINCT meta_key
 FROM wp_5_postmeta
 WHERE meta_key NOT BETWEEN '_' AND '_z'
 HAVING meta_key NOT LIKE '\_%'
 ORDER BY meta_key
 limit 30;''
 '''167 seconds'''

 4.1 query, UTF8 meta_key (collation and encoding):
 '''801 milliseconds'''

 4.3 query, UTF8 meta_key (collation and encoding):
 '''860 milliseconds'''

 4.1 query, UTF8mb4, VARCHAR(191)
 '''1.49 seconds'''

 4.3 query, UTF8mb4, VARCHAR(191)
 '''1.5 seconds'''

 Doing an EXPLAIN on these queries confirms that before the VARCHAR(191)
 change, the query would not use the index when UTF8mb4 formatted.   After
 the change, it uses the correct meta_key index (just as it did when UTF8
 formatted).

 From reading the MySQL documentation, this is not how I would expect
 indexes to work.  The behavior described in the documentation seems to say
 that for a VARCHAR(255), the index would simply use the first 191
 characters for the index and ignore the rest.  I have not yet seen
 anything that suggests changing the field format itself is necessary.

 @tollmanz's testing suite is pretty solid, but I've got a number of real
 sites with large postmeta tables impacted by this problem that I can test
 patches on if that would be helpful.

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