[wp-trac] [WordPress Trac] #31071: post_mime_type related queries still slow on larger sites

[WordPress Trac]

#31071: post_mime_type related queries still slow on larger sites
------------------------------+--------------------------
 Reporter:  archon810         |       Owner:  pento
     Type:  defect (bug)      |      Status:  assigned
 Priority:  normal            |   Milestone:  4.3
Component:  Media             |     Version:  4.1
 Severity:  normal            |  Resolution:
 Keywords:  has-patch commit  |     Focuses:  performance
------------------------------+--------------------------
Changes (by wonderboymusic):

 * keywords:  needs-patch => has-patch commit
 * owner:   => pento
 * status:  new => assigned
 * milestone:  Future Release => 4.3


Comment:

 [attachment:31071.diff 31071.diff] adds the index, I think I did it
 correctly. @pento, please code-review this.

 To recap:
 When you have a LOT of attachments, these queries require what is
 essentially a full table scan because there is no index containing
 `post_mime_type`. I tested this by adding over 1 million rows to the post
 table with `attachment` as `post_type` and a variety of image mime-types
 as `post_mime_type`. I then changed ONE (1) row to have a `post_mime_type`
 of `audio/mp3` at ID 700,000.

 Before index:
 Queries took ~2.5-3 seconds each

 After index:
 Queries took 0.7 milliseconds each

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