[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 Denis-de-Bernardy):

 Fwiw, this type of query (all section index generating types of queries,
 really):

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

 Can be rewritten by completing it in two separate steps.

 In a first fetch the two bounds:

 {{{
 select min(post_date) as min_date, max(post_date) as max_date from
 wp_posts where post_type = 'attachment';
 }}}

 Then use php to generate all possible months:

 {{{
 $months = array('2014-01-01', '2014-02-01', ...);
 }}}

 And then use these months to rewrite the query like:

 {{{
 select year(min_bound), month(min_bound)
 from (
   select cast('2014-01-01' as date) as min_bound, cast('2014-02-01' as
 date) as max_bound
   union all
   select cast('2014-02-01' as date) as min_bound, cast('2014-02-01' as
 date) as max_bound
   union all
   ...
 ) as bounds
 where exists (
   select 1
   from wp_posts
   where post_type = 'attachment'
   and post_date >= min_bound
   and post_date < max_bound
 )
 order by min_bound desc
 }}}

 The general idea, in other words, is to get a query plan that does N reads
 within an index.

 (With lateral queries and set generators, you can write an equivalent
 query in one go, but MySQL doesn't offer lateral queries or proper
 generators.)

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