[wp-trac] [WordPress Trac] #14699: Optimize SQL resulting from custom taxonomy queries

[WordPress Trac]

#14699: Optimize SQL resulting from custom taxonomy queries
--------------------------+-------------------------------------------------
 Reporter:  misterbisson  |       Owner:                         
     Type:  enhancement   |      Status:  new                    
 Priority:  normal        |   Milestone:  Awaiting Review        
Component:  Query         |     Version:  3.0.1                  
 Severity:  normal        |    Keywords:  has-patch needs-testing
--------------------------+-------------------------------------------------

Comment(by misterbisson):

 Replying to [comment:5 markjaquith]:

 The subquery is indeed faster. I ran some tests against a table set with
 about 15,000 terms, 181,000 term relationships, and 19,000 posts (running
 on a default MySQL config on a MacBook Pro). Here are the results:

 {{{
 SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.*
 FROM wp_posts
 INNER JOIN wp_term_relationships tax_tr ON (wp_posts.ID =
 tax_tr.object_id)
 INNER JOIN wp_term_taxonomy tax_tt ON (tax_tr.term_taxonomy_id =
 tax_tt.term_taxonomy_id)
 WHERE 1=1
 AND tax_tt.taxonomy = 'channel'
 AND tax_tt.term_id IN ('334540')
 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 0, 10;
 }}}

 This repeatedly returned results in about .4 seconds

 {{{
 SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.*
 FROM wp_posts
 WHERE 1=1
 AND wp_posts.ID IN (
         SELECT SQL_NO_CACHE tax_tr.object_id
         FROM wp_term_relationships tax_tr
         INNER JOIN wp_term_taxonomy tax_tt ON (tax_tr.term_taxonomy_id =
 tax_tt.term_taxonomy_id)
         WHERE tax_tt.taxonomy = 'channel'
         AND tax_tt.term_id IN ('334540')
 )
 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 0, 10;
 }}}

 This repeatedly returned results in about .08 seconds

 The subquery approach is likely also better suited to doing intersections.

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