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

 >Any thoughts on making post_id the primary index (easier) or changing the
 way WordPress retrieves meta data (faster)?

 This is impossible, since post id is not unique in meta.

 >Have you tested post_id, meta_key against meta_key, post_id?

 The latter is faster, since generally you don't query by multiple post ids
 with a range or LIKE.

 ---

 Looking at WooCommerce HPOS I see that they even use the meta value in an
 index, which does make sense, e.g. for range queries.

 I think the existing "meta_key" only index can be dropped, since that's
 barely used.
 I guess a meta_key, meta_value index like they use in WooCommerce HPOS
 would make more sense.
 In addition to the meta_key, post_id index, potentially making this a 3
 index as meta_key, meta_value, post_id

 Since we've been using the initial index mentioned above since years, I
 don't have any benchmarks anymore and currently no time to get this
 further. Once we do performance analysis again in a few months and we
 still see room for improvement or a bottleneck, I'll provide a benchmark
 and a PR maybe via upgrade.php.

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