[wp-trac] [WordPress Trac] #45354: Adding index for meta_key and post_id in postmeta

[WordPress Trac]

#45354: Adding index for meta_key and post_id in postmeta
----------------------------------------+------------------------------
 Reporter:  DuckDagobert                |       Owner:  (none)
     Type:  enhancement                 |      Status:  new
 Priority:  normal                      |   Milestone:  Awaiting Review
Component:  Database                    |     Version:
 Severity:  normal                      |  Resolution:
 Keywords:  needs-testing dev-feedback  |     Focuses:  performance
----------------------------------------+------------------------------

Comment (by fliespl):

 @galbaras I don't have any big performance results for this index, but
 post_id + meta_key (80) was a winner in my case.

 I have used limitation of 80 length for meta_key to reduce index space
 usage + because 99% of meta_keys were well below that value, while testing
 on a medium sized site (100k+ posts).

 https://ss.codeone.pl/ss-2023-12-18-22-42-24-1702935744-a7HMU6OU.png

 Most of the use cases for postmeta I have seen on many sites involved
 combination of post_id + meta_key (like filtering posts for specific ACF
 value or woocommerce value).


 Also I disagree about post_id primary key (It's not even currently
 possible - it would totally change structure given that you have multiple
 meta_keys for single post + you can have duplicates! on post_id meta_key
 combination). meta_id makes sense here, but combination of post+meta_key
 index as well.

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