[wp-trac] [WordPress Trac] #8351: Poorly optimized queries in wp_get_calendar()

[WordPress Trac]

#8351: Poorly optimized queries in wp_get_calendar()
-------------------------------------+--------------------------------------
 Reporter:  vladimir_kolesnikov      |        Owner:  anonymous
     Type:  enhancement              |       Status:  reopened 
 Priority:  normal                   |    Milestone:  3.0      
Component:  Optimization             |      Version:           
 Severity:  normal                   |   Resolution:           
 Keywords:  has-patch tested commit  |  
-------------------------------------+--------------------------------------
Changes (by Denis-de-Bernardy):

  * keywords:  has-patch needs-testing => has-patch tested commit


Comment:

 calendar is working fine and as expected here. calendar queries run as so:

 without:

 {{{
 SELECT DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
                 FROM www_posts
                 WHERE post_date < '2009-11-01'
                 AND post_type = 'post' AND post_status = 'publish'
                         ORDER BY post_date DESC
                         LIMIT 1

 0.7ms

 SELECT  DISTINCT MONTH(post_date) AS month, YEAR(post_date) AS year
                 FROM www_posts
                 WHERE post_date >       '2009-11-01'
                 AND MONTH( post_date ) != MONTH( '2009-11-01' )
                 AND post_type = 'post' AND post_status = 'publish'
                         ORDER   BY post_date ASC
                         LIMIT 1

 0.8ms

 SELECT DISTINCT DAYOFMONTH(post_date)
                 FROM www_posts WHERE MONTH(post_date) = '11'
                 AND YEAR(post_date) = '2009'
                 AND post_type = 'post' AND post_status = 'publish'
                 AND post_date < '2010-03-01 19:58:02'

 1.4ms

 SELECT post_title, DAYOFMONTH(post_date) as dom FROM www_posts WHERE
 YEAR(post_date) = '2009' AND MONTH(post_date) = '11' AND post_date <
 '2010-03-01 19:58:02' AND post_type = 'post' AND post_status = 'publish'

 7.4ms

 }}}

 with:

 {{{
 SELECT MONTH(post_date) AS month, YEAR(post_date) AS year
                 FROM www_posts
                 WHERE post_date < '2009-11-01'
                 AND post_type = 'post' AND post_status = 'publish'
                         ORDER BY post_date DESC
                         LIMIT 1

 0.7ms

 SELECT MONTH(post_date) AS month, YEAR(post_date) AS year
                 FROM www_posts
                 WHERE post_date > '2009-11-30 23:59:59'
                 AND post_type = 'post' AND post_status = 'publish'
                         ORDER BY post_date ASC
                         LIMIT 1

 0.8ms

 SELECT DISTINCT DAYOFMONTH(post_date)
                 FROM www_posts WHERE post_date >= '2009-11-01 00:00:00'
                 AND post_type = 'post' AND post_status = 'publish'
                 AND post_date <= '2009-11-30 23:59:59'

 0.6ms

 SELECT post_title, DAYOFMONTH(post_date) as dom FROM www_posts WHERE
 post_date >= '2009-11-01 00:00:00' AND post_date <= '2009-11-30 23:59:59'
 AND post_type = 'post' AND post_status = 'publish'

 0.8ms

 }}}

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/8351#comment:17>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software