[wp-trac] [WordPress Trac] #17075: tag pages slow after 3.1.1. upgrade

[WordPress Trac]

#17075: tag pages slow after 3.1.1. upgrade
-------------------------------+------------------------------
 Reporter:  softclick          |       Owner:
     Type:  defect (bug)       |      Status:  new
 Priority:  normal             |   Milestone:  Awaiting Review
Component:  Taxonomy           |     Version:  3.1.1
 Severity:  normal             |  Resolution:
 Keywords:  reporter-feedback  |
-------------------------------+------------------------------

Comment (by softclick):

 No, the problem happens with single-tag pages.  (e.g. /tag/foo)

 If we use the query.php of 3.1 everything is fine. (< 1 second) If we use
 the 3.1.1 version a tag-page with let's say 400+ depending posts takes
 "forever". (10-20 seconds)

 Here is the output of the slow query log:

 {{{
 # Time: 110407 23:14:24
 # User at Host: web1[web1] @ localhost []
 # Query_time: 14  Lock_time: 0  Rows_sent: 10  Rows_examined: 14764793
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND (
 wp_posts.ID NOT IN (
                                         SELECT object_id
                                         FROM wp_term_relationships
                                         WHERE term_taxonomy_id IN (7106)
                                 ) AND wp_posts.ID IN (
                                         SELECT object_id
                                         FROM wp_term_relationships
                                         WHERE term_taxonomy_id IN (7)
                                         GROUP BY object_id HAVING
 COUNT(object_id) = 1
                                 ) ) AND wp_posts.post_type = 'post' AND
 (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')
 GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 20, 10;
 }}}

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