[wp-trac] [WordPress Trac] #41281: attachment_url_to_postid results in very slow query

[WordPress Trac]

#41281: attachment_url_to_postid results in very slow query
------------------------------+------------------------------
 Reporter:  Takahashi_Fumiki  |       Owner:  joemcgill
     Type:  enhancement       |      Status:  accepted
 Priority:  normal            |   Milestone:  Awaiting Review
Component:  Database          |     Version:  4.9
 Severity:  normal            |  Resolution:
 Keywords:                    |     Focuses:  performance
------------------------------+------------------------------

Comment (by apermo):

 I ran into the exact same issue as stated in #61651 and #61383.

 Quote from #61651

  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).
 ''Edit: the longest was over 6s''
  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}}}

 I hope to restart the discussion in order to improve things for everyone.
 As mentioned in the other topic, with adding the filter proposed in
 #61383, there is a way to improve things for people who are aware of the
 solution. But it would be far from the ideal solution.

 Does anyone have a third solution besides adding an index to
 {{{wp_postmeta}} or adding the path as column to {{{wp_posts}}}.

 In my example I went for adding a custom table, that will only contain the
 post_id and the attachment_file_path, which speeds things up from 3-6s
 down to 2ms, while I think this was the best choice for my clients
 project, I don't think this would be the perfect solution for WordPress.

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