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

 Ran into this issue as well on a large site where posts with lots of
 images after we added more calls to attachment_url_to_postid() started
 taking 60s+ (issue caught on staging, thankfully), and the simplest and
 fastest solution which I already put together years ago on another site
 and forgot all about was indeed to add a combined index on meta_key,
 meta_value.

 I went as far as using 191 - the max allowed, for both key and value, and
 all the perf issues were resolved instantly. It should be good enough for
 most sites unless you have a lot of urls that share the same exact first
 191 characters - highly unlikely.

 This should be the solution in the core. If the core team does decide to
 go with this solution (which I think it should), there's no point creating
 another index - the stock meta_key index should just be converted into the
 composite index instead.

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