[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        |   Keywords:
  Focuses:  performance   |
--------------------------+-----------------------------
 Hey guys,

 Remember #27985? It was about a slow query that did probably a full table
 scan, which was then replaced with two queries:
 {{{
 SELECT ID
                 FROM wp_posts
                 WHERE post_type = 'attachment'
                 AND post_mime_type LIKE 'video%'
                 LIMIT 1
 }}}

 {{{
 SELECT ID
                 FROM wp_posts
                 WHERE post_type = 'attachment'
                 AND post_mime_type LIKE 'audio%'
                 LIMIT 1
 }}}

 On a busy server with 285k entries in wp_posts, this query is taking 5-7s
 each. And it takes forever to save/open an edit post screen.

 The main problem is that there's not a proper index for it to use.

 Running EXPLAIN shows this:
 {{{
 +----+-------------+----------+------+------------------+------------------+---------+-------+--------+-------------+
 | id | select_type | table    | type | possible_keys    | key
 | key_len | ref   | rows   | Extra       |
 +----+-------------+----------+------+------------------+------------------+---------+-------+--------+-------------+
 |  1 | SIMPLE      | wp_posts | ref  | type_status_date | type_status_date
 | 62      | const | 161195 | Using where |
 +----+-------------+----------+------+------------------+------------------+---------+-------+--------+-------------+
 1 row in set
 }}}

 I'm not really sure why such an unoptimized query would be put in place,
 but the index on (`post_type`, `post_mime_type`) fixes it:
 {{{
 +----+-------------+----------+-------+--------------------------------------+---------------------+---------+------+------+-------------+
 | id | select_type | table    | type  | possible_keys
 | key                 | key_len | ref  | rows | Extra       |
 +----+-------------+----------+-------+--------------------------------------+---------------------+---------+------+------+-------------+
 |  1 | SIMPLE      | wp_posts | range |
 type_status_date,post_type_mime_type | post_type_mime_type | 364     |
 NULL |    1 | Using where |
 +----+-------------+----------+-------+--------------------------------------+---------------------+---------+------+------+-------------+
 1 row in set
 }}}

 The query now runs in 0.2s.

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