[wp-trac] [WordPress Trac] #53320: Why delete_metadata and update_metadata slow performance?

[WordPress Trac]

#53320: Why delete_metadata and update_metadata slow performance?
--------------------------+-----------------------------
 Reporter:  cjj25         |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:
 Severity:  normal        |   Keywords:  needs-dev-note
  Focuses:  performance   |
--------------------------+-----------------------------
 I’ve been doing some investigating on the overall performance of a local
 WordPress with WooCommerce build and something doesn’t seem to be making
 sense, I’m not sure if I’m missing something or if it’s an oversight in
 the development.

 WooCommerce has ways of checking if meta keys and values need to be
 updated, for example update_post_meta in class-wc-order-data-store-cpt.php

 The plugin also has a method update_or_delete_post_meta for handling the
 meta of posts integration with the WordPress Core.

 Here’s where the confusion comes in,
 1.
 [delete_metadata](https://github.com/WordPress/WordPress/blob/21cf92796123e98bcc9cc7981d80472977673fab
 /wp-includes/meta.php#L335)
 Why does delete_metadata by default query to see if it exists before
 running the DELETE operation? I understand the DELETE operation is
 expensive but in this instance, we know the keys that are going to be
 removed. I understand we could hook into the delete_{$meta_type}_metadata
 filter, return false and run the query ourselves but this doesn’t future
 proof things. I propose an additional filter and/or argument that allows
 you to bypass the initial lookup.

 2.
 [update_metadata](https://github.com/WordPress/WordPress/blob/21cf92796123e98bcc9cc7981d80472977673fab
 /wp-includes/meta.php#L159)
 There is a similar problem here too. [Every update queries the
 database](https://github.com/WordPress/WordPress/blob/21cf92796123e98bcc9cc7981d80472977673fab
 /wp-includes/meta.php#L220) to see if a row already exists and if so, then
 use the add_metadata. If we know the meta key 100% exists, why go to the
 expense of then querying the database again? Once again, I appreciate this
 is added as a fail-safe to ensure the data gets stored but this leaves the
 developer with no “official” way of updating the meta without having to do
 a lookup first. Again, we can hook into update_{$meta_type}_metadata but
 this has the same problem as before.. breaking future changes. I propose
 the same as above for this method too.

 Has anybody else experienced this or found any solutions to excessive SQL
 lookups (other than relying on cache)?

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/53320>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform