[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
------------------------------+--------------------------

Comment (by archon810):

 Yeah, it's still a problem for me, on both of the above versions.

 Without post_type_date:


 {{{
 explain SELECT SQL_NO_CACHE DISTINCT YEAR( post_date ) AS year, MONTH(
 post_date ) AS month
                 FROM wp_posts
                 WHERE post_type = 'attachment'
                 ORDER BY post_date DESC;

 }}}


 {{{
 +----+-------------+----------+------+------------------------------------------------+---------------------+---------+-------+--------+---------------------------------------------------------------------+
 | id | select_type | table    | type | possible_keys
 | key                 | key_len | ref   | rows   | Extra
 |
 +----+-------------+----------+------+------------------------------------------------+---------------------+---------+-------+--------+---------------------------------------------------------------------+
 |  1 | SIMPLE      | wp_posts | ref  |
 type_status_date,post_date,post_type_mime_type | post_type_mime_type | 62
 | const | 150322 | Using index condition; Using where; Using temporary;
 Using filesort |
 +----+-------------+----------+------+------------------------------------------------+---------------------+---------+-------+--------+---------------------------------------------------------------------+
 1 row in set
 }}}

 {{{


 mysql> show profiles;
 +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Query_ID | Duration   | Query
 |
 +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 |        1 | 1.74982625 | SELECT SQL_NO_CACHE DISTINCT YEAR( post_date )
 AS year, MONTH( post_date ) AS month
                 FROM wp_posts
                 WHERE post_type = 'attachment'
                 ORDER BY post_date DESC |
 +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 1 row in set
 }}}

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