[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        |  Resolution:
 Keywords:                |     Focuses:  performance
--------------------------+------------------------------

Comment (by mboynes):

 I'm currently building a site with about 1.6 million images and I've run
 into this issue again as well.

 Adding the (post_type, post_mime_type) index did work for me as well, and
 drops each query from about 30s (!) to 1ms. MySQL 5.5.41 didn't use the
 wrong index for me in this query:

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

 However, that query is still very slow (~1.5s) for me.

 I'm not sure there's a way to optimize this query, so I think we need to
 look elsewhere to fix it. It looks like the purpose is to populate the
 date filtering dropdown in the "Insert Media" dialog, which to be honest
 I've never even noticed. My suggestions would be to:

 1. replace this query with one to find the oldest date (`MIN(post_date)`),
 then add an entry for every month/year since then;
 2. similar to (1), run one query to find the oldest year
 (`YEAR(MIN(post_date))`), and split the filter into two dropdowns, one for
 year and one for month;
 3. add a layer of caching to this; or
 4. make this optionally filterable so small/medium sites can continue to
 use this as-is, and large sites can opt out of it.

 I think I lean toward (2), as that dropdown can be unwieldy anyway for
 active sites with longstanding archives. (3) is probably the best option
 to maintain the existing functionality; this data could be stored in an
 option, and when a new image is added, it could be checked for that year-
 month combination and add it if need be.

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