[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:
     Type:  enhancement       |     Status:  new
 Priority:  normal            |  Milestone:  Awaiting Review
Component:  Database          |    Version:  trunk
 Severity:  normal            |   Keywords:
  Focuses:  performance       |
------------------------------+-----------------------------
 `attachment_url_to_postid` throws query like this:

 {{{
 # wp-includes/media.php ll.3922
 $sql = $wpdb->prepare(
         "SELECT post_id FROM $wpdb->postmeta WHERE meta_key =
 '_wp_attached_file' AND meta_value = %s",
         $path
 );
 }}}

 But wp_postmeta table has index only of `meta_key`, this may cause file
 sort.

 == Why this is problem ==

 Let's assume that you have 20,000 posts and each post has 10 attachments
 in your WP news site.
 MySQL filters rows with `meta_key` but still remain 200,000 possible rows.
 This causes file sort.

 `attachment_url_to_postid` is used not only in admin screen, but also in
 public area(e.g. AMP Plugin https://wordpress.org/plugins/amp/ ).

 Now twitter changes URL for mobile device if AMP version is available.
 So, MySQL CPU raises up to 100% by `attachment_url_to_postid` if site
 traffic is high,

 == Solution ==

 To avoid slow query, I suggest adding another index.

 {{{
 ALTER TABLE $wpdb->postmeta ADD INDEX meta_key_meta_value (meta_key(191),
 meta_value (64));
 }}}

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