[wp-trac] [WordPress Trac] #16706: Queries using "category__and" are slow on large databases

[WordPress Trac]

#16706: Queries using "category__and" are slow on large databases
-------------------------+-----------------------------
 Reporter:  tigertech    |       Owner:  scribu
     Type:  enhancement  |      Status:  reviewing
 Priority:  normal       |   Milestone:  Future Release
Component:  Performance  |     Version:  3.1
 Severity:  normal       |  Resolution:
 Keywords:  needs-patch  |
-------------------------+-----------------------------
Changes (by scribu):

 * keywords:  close => needs-patch
 * type:  defect (bug) => enhancement
 * component:  Database => Performance
 * milestone:  Awaiting Review => Future Release


Comment:

 By the way, you can get the more efficient query I was talking about by
 using the more verbose syntax:

 {{{
 query_posts(array(
   'tax_query' => array(
     array( 'taxonomy' => 'category', 'terms' => 1 ),
     array( 'taxonomy' => 'category', 'terms' => 461 ),
 ));
 }}}

 {{{
 SELECT SQL_CALC_FOUND_ROWS wp_posts . *
 FROM wp_posts
 INNER JOIN wp_term_relationships ON ( wp_posts.ID =
 wp_term_relationships.object_id )
 INNER JOIN wp_term_relationships AS tt1 ON ( wp_posts.ID = tt1.object_id )
 WHERE 1 =1
 AND (
 wp_term_relationships.term_taxonomy_id
 IN ( 38 )
 AND tt1.term_taxonomy_id
 IN ( 37 )
 }}}

 || SIMPLE || wp_term_relationships || ref || PRIMARY,term_taxonomy_id ||
 term_taxonomy_id || 8 || const || 1 || Using temporary; Using filesort ||
 || SIMPLE || tt1 || eq_ref || PRIMARY,term_taxonomy_id || PRIMARY || 16 ||
 wp-trunk.wp_term_relationships.object_id,const || 1 || Using index ||
 || SIMPLE || wp_posts || eq_ref || PRIMARY,type_status_date || PRIMARY ||
 8 || wp-trunk.wp_term_relationships.object_id

 So, it's just a matter of making the {{{'category__and'}}} rewrite
 internally to that more efficient way.

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