[wp-trac] [WordPress Trac] #61651: attachment_url_to_postid() performs possibly slow SELECT against meta_value

[WordPress Trac]

#61651: attachment_url_to_postid() performs possibly slow SELECT against meta_value
----------------------------+-----------------------------
 Reporter:  apermo          |      Owner:  (none)
     Type:  enhancement     |     Status:  new
 Priority:  normal          |  Milestone:  Awaiting Review
Component:  Media           |    Version:  trunk
 Severity:  normal          |   Keywords:
  Focuses:  sustainability  |
----------------------------+-----------------------------
 The function {{{attachment_url_to_postid()}}} in {{{wp-
 includes/media.php}}} will perform this query.
 {{{
 SELECT post_id, meta_value FROM wp_postmeta WHERE meta_key =
 '_wp_attached_file' AND meta_value = %s;
 }}}

 While this query looks super harmless, it can be really really slow. On a
 site that I maintain, this query takes a total of 4s (four seconds).

 As I already described in #61383 this issue will happen on large scale
 websites with  several 100k attachments. And even though this is an edge
 case, I would like to spark an open discussion about it.

 In my case, I've verified an index on meta_value, which improved the query
 from 4s to approx. 5-10ms, I lacked the confidence to do so, so I
 introduced a custom table for as lookup table, which improved the query
 down to 2ms. I would have been absolutely happy with the 5-10ms though.

 I still need to check wether the extensive calls of
 {{{attachment_url_to_postid()}}} where caused by WordPress core, or a
 third Party plugin. I will add this information as soon as possible.

 While it may not slow down smaller sites as much as it slowed down my
 clients site, it will on a larger scale, still burn a noticeable amount of
 CPU time, that will ultimately consume power and thus produce CO2.

 Following a topic on GitHub regarding performance. Otto42 replied to the
 suggestion to add indexes to {{{wp_postmeta}}} with

   ''Indexing the meta_value is a bad idea, because you should not be
 searching for anything based on the meta_value in the first place.''
   ---
   Otto42 commented on Apr 17, 2022
 https://github.com/WordPress/performance/issues/132#issuecomment-1100829520


 At WordCamp Europe 2024 in Torino, @luehrsen asked in the Q&A about
 database improvement. https://www.youtube.com/watch?v=RxQ5yhsxig4&t=429s

 I can completely agree with Matts reply, that the WordPress database is
 extremely flexible and any changes should be made with care.
 But since there is a contradiction between "should not be searching for
 anything base on the meta_value" and what happens inside
 {{{attachment_url_to_postid()}}}, I think we should discuss and improve
 this.

 I have no clue what the best solution could be.

 A few further thoughts to spark the discussion.

 {{{wp_posts}}} does have a column {{{post_mime_type}}}.
 Why is the mime type in {{{wp_posts}}} and while the path to the attached
 file is in meta? Could it be the solution to make a column
 {{{attached_file}}}?
 The problem with this is the balance between redundancy and backwards
 compatibility. Since there will be plugins directly accessing
 {{{_wp_attached_file}}} in {{{wp_postmeta}}} this would require filters to
 make {{{get_post_meta()}}} still return {{{_wp_attached_file}}} and
 {{{set_post_meta()}}} write it. So while I really like this approach, I
 think this one has a lot special cases and possible side effects.

 Indices in MySQL have a length what they index, {{{meta_value}}} for
 {{{_wp_attached_file}}} will contain something like {{{2024/07/my-fancy-
 foto-with-a-speaking-name.jpg}}} (46 characters). So adding even adding a
 short index, like 10-20 characters long, would already be a gain here.
 This would likely solve any issues with most queries against
 {{{wp_postmeta}}}

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