[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:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Media         |     Version:  4.1
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  performance
--------------------------+------------------------------

Comment (by archon810):

 I'm noticing a side effect of this new index - MySQL is being dumb and is
 using this new index on this query, which now runs really slow (15s):

 {{{
 SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
                 FROM wp_posts use index (type_status_date)
                 WHERE post_type = 'attachment'
                 ORDER BY post_date DESC;
 }}}

 instead of the original `type_status_date` index which runs about 30 times
 faster for me (using `USE INDEX (type_status_date)`.

 It's quite frustrating, because using `type_status_date` results in `Using
 where; Using index; Using temporary; Using filesort` whereas using
 `post_type_mime_type` results in `Using where; Using temporary; Using
 filesort` - 'using index' is no longer there. It examines fewer rows, but
 the performance hit is huge. I'm surprised MySQL isn't smart enough here.

 I'm going to play around with this some more to see if I can make an even
 better index to satisfy this query.

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