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

 @jamesmehorter Glad you were able to confirm the original performance
 issues and much better performance after the new index.

 As for the months/years query, I'm currently running MySQL 5.6.28 and I
 indeed can't reproduce the slowdown anymore. The site is the same, now
 with even more data. I'm using "IGNORE INDEX" to test what happens if
 `post_type_date` is ignored, and I'm no longer seeing
 `post_type_mime_type` index being used by MySQL automatically - instead it
 uses `type_status_date`.

 The query that ignores `post_type_date` (i.e. the way WP is currently
 working) returns about 1.5x faster. Why it was using `post_type_mime_type`
 during earlier tests is beyond me - the MySQL query optimizer works in
 mysterious ways. But I wouldn't be surprised if it kicked in for other
 users with whatever data conditions that were causing
 `post_type_mime_type` to be used here before for me.

 Results attached.

 {{{
 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 | 192321 | Using where; Using index;
 Using temporary; Using filesort |
 +----+-------------+----------+------+---------------------------------------------------------------+----------------+---------+-------+--------+-----------------------------------------------------------+
 1 row in set
 }}}

 {{{
 EXPLAIN SELECT SQL_NO_CACHE DISTINCT
         YEAR (post_date) AS YEAR,
         MONTH (post_date) AS MONTH
 FROM
         wp_posts IGNORE INDEX (post_type_date)
 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 |
 type_status_date | 62      | const | 167406 | Using where; Using index;
 Using temporary; Using filesort |
 +----+-------------+----------+------+---------------------------------------------------------------+------------------+---------+-------+--------+-----------------------------------------------------------+
 1 row in set
 }}}

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