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

 Sorry for the delay.  I'll try to provide numbers (`EXPLAIN` before vs.
 after) based on the latest patch + the most recent site that suffers from
 this issue (again!) as soon as possible.  If anyone else beats me to it,
 go for it!


 @jamesmehorter:
 > 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.

 Happy to adjust if others agree with your stance, but as mentioned before,
 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).  Both chances are pretty
 much close to zero, so I do accept your bets :-)

 I'd go with `USE INDEX` if there was an ideal index to use.  But WordPress
 Core does not ship with an ideal index, so `IGNORE INDEX` is the most
 logical thing to do.  No?

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