[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
------------------------------+--------------------------
Changes (by archon810):

 * keywords:  has-patch => has-patch commit


Comment:

 With post_type_date:

 {{{
 mysql> 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_date |
 post_type_date | 62      | const | 177603 | Using where; Using index;
 Using temporary; Using filesort |
 +----+-------------+----------+------+---------------------------------------------------------------+----------------+---------+-------+--------+-----------------------------------------------------------+
 1 row in set
 }}}

 {{{
 mysql> show profiles;
 +----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 | Query_ID | Duration   | Query
 |
 +----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 |        3 | 0.58336925 | 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         |
 +----------+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 }}}

 It's not ideal, but the query in this case runs 3x faster. I've seen cases
 where the query without post_type_date present ran in 10+s though,
 depending on server conditions and caching, whereas with post_type_date,
 it seems to be pretty consistently fast (still not fast enough, which is
 why I think it should be abolished).

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