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

 I have the some problem, I have a website with 2.000.000 rows in
 wp_postmeta. If I select all rows it take 0.00002s, but with the query
 generated by attachment_url_to_postid from elementor it take 0.4s slowing
 the home generation up to 20s

 SELECT post_id, meta_value
 FROM wp_postmeta
 WHERE meta_key = '_wp_attached_file'
 AND meta_value = '2020/03/almo-nature.svg'

 The problem is that this table is indexed for post_id, meta_vale and
 meta_key but NOT for meta_value that is used from this function.

 I solved it adding an index(100) to the meta_value and changed from
 longtext to text, I don't think that this column need 4,294,967,295
 characters. Isn't text 65,535 characters enough? Isn't varchar faster?

 Thanks

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