[wp-trac] [WordPress Trac] #53958: Database: wp_postmeta - meta_key index

[WordPress Trac]

#53958: Database: wp_postmeta - meta_key index
--------------------------+------------------------------
 Reporter:  wishe         |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:  5.8
 Severity:  major         |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------

Comment (by wishe):

 Replying to [comment:2 iCaleb]:
 > Related comment in core:
 https://github.com/WordPress/WordPress/blob/1b7cd950ca6807996996e9a30c98298eb5073bce
 /wp-admin/includes/schema.php#L49-L51

 This explains the reasoning of why it was done, but with the extreme
 performance hit that this causes there has to be another solution.

 I ran a few tests today and the results are absolutely shocking.

 This is the query with the index restricted to 191
 [[Image(https://i.ibb.co/4Z5bHxG/Screenshot-2021-08-25-11-38-57.png)]]

 This is after removing the character limit on the index
 [[Image(https://i.ibb.co/cN961rs/Screenshot-2021-08-25-11-34-17.png)]]

 This is running on a large WooCommerce store with close to 2 million
 records in the wp_postmeta table. And the difference is staggering

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