[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:  Future Release
Component:  Media         |     Version:  4.1
 Severity:  normal        |  Resolution:
 Keywords:  has-patch     |     Focuses:  performance
--------------------------+-----------------------------

Comment (by pento):

 Because the query doesn't have an `ORDER BY`, it returns as soon as it
 finds the first result, via table scan. If there are no results, it needs
 to scan the entire table to determine that.

 To avoid that scan, I suspect an index is going to be the solution.

 That brings us back to the previous problem, adding this index causes
 MySQL to incorrectly choose it for this query, over the `type_status_date`
 index:

 {{{
 SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
 FROM $wpdb->posts
 WHERE post_type = 'attachment'
 ORDER BY post_date DESC
 }}}

 It's not using the index we want it to, because the query doesn't
 reference the `post_status` column.

 If we rewrite the query like so, MySQL should choose the correct index:

 {{{
 SELECT DISTINCT YEAR( post_date ) AS year, MONTH( post_date ) AS month
 FROM $wpdb->posts
 WHERE post_type = 'attachment'
 AND post_status = 'inherit'
 ORDER BY post_date DESC
 }}}

 @archon810, can you see if that query works better after removing your
 `post_type_date` index?

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