[wp-trac] [WordPress Trac] #31071: media / post_mime_type related queries are very slow on larger sites

[WordPress Trac]

#31071: media / post_mime_type related queries are very slow on larger sites
-------------------------------------------------+-------------------------
 Reporter:  archon810                            |       Owner:  pento
     Type:  defect (bug)                         |      Status:  assigned
 Priority:  normal                               |   Milestone:  Future
Component:  Media                                |  Release
 Severity:  normal                               |     Version:  4.1
 Keywords:  dev-feedback reporter-feedback 2nd-  |  Resolution:
  opinion has-patch                              |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------

Comment (by jamesmehorter):

 Hey @tha_sun, thanks for pushing this forward some more..

 >> Using IGNORE INDEX (post_type_mime_type) seems like it could fail in
 the future if other indexes are added. I'd rather see a hint to USE INDEX
 or a post_status clause as @pento suggested.
 > [...] the current index that MySQL happens to choose is NOT the most
 ideal index for the query in question. [...] The chance of a random site
 owner (intentionally) adding a custom index that happens to be the ideal
 index for this query is equal to the chance of WordPress Core adding a new
 index that (unintentionally) happens to be preferred by the MySQL query
 optimizer (again).

 Hehe, that's a lot of chances :) We're adding a new index now, and I
 suppose there's always a chance we could choose to add another index in
 the future. Also, to your point, MySQL '''is''' choosing the wrong
 index—to me it makes sense for us to specify exactly which index to use.
 Though, like you I'll side with the majority here; that was just a
 suggestion on how I'd personally tackle the problem.

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