[wp-trac] [WordPress Trac] #17488: Extremely inefficient queries in archives for multiple tags (terms)

[WordPress Trac]

#17488: Extremely inefficient queries in archives for multiple tags (terms)
--------------------------+-----------------------------
 Reporter:  nkuttler      |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Query         |    Version:  3.1.2
 Severity:  major         |   Keywords:  needs-patch
--------------------------+-----------------------------
 A subquery in FROM is executed once, a subquery in WHERE is executed for
 each row. This could be leveraged to massively increase the query
 efficiency for archives with multiple tags. At the moment archives for
 multiple tags are pretty useless once you have more than a few hundred
 posts.

 Example:

 {{{
 #!sql
 SELECT SQL_CALC_FOUND_ROWS wp_public_posts.* FROM wp_public_posts
 WHERE 1=1 AND
 ( wp_public_posts.ID IN (
   SELECT object_id FROM wp_public_term_relationships
   WHERE term_taxonomy_id IN (170,127)
   GROUP BY object_id
   HAVING COUNT(object_id) = 2
 ) )
 AND wp_public_posts.post_type = 'post'
 AND (wp_public_posts.post_status = 'publish' OR
 wp_public_posts.post_status = 'private')
 GROUP BY wp_public_posts.ID ORDER BY wp_public_posts.post_date DESC LIMIT
 0, 10;
 }}}

 --> 2 rows in set (13.92 sec)

 By simply doing the WHERE SELECT query once in advance we can use this
 query instead:

 {{{
 #!sql
 SELECT SQL_CALC_FOUND_ROWS wp_public_posts.* FROM wp_public_posts
 WHERE 1=1 AND ( wp_public_posts.ID IN (
   3614, 3740
  ) )
 AND wp_public_posts.post_type = 'post'
 AND (wp_public_posts.post_status = 'publish' OR
 wp_public_posts.post_status = 'private')
 GROUP BY wp_public_posts.ID ORDER BY wp_public_posts.post_date DESC LIMIT
 0, 10;
 }}}
 --> 2 rows in set (0.00 sec)

 So I just decreased the page load time by almost 14 seconds. Yes, the
 query cache was reset for this.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/17488>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software