[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:  has-patch    |
-------------------------+-----------------------------

Comment (by tigertech):

 Replying to [comment:5 scribu]:
 > Here's an experiment to try on a clean install:
 >
 > Make two categories A and B.
 >
 > Make Post 1 and assign it both category A and category B.
 >
 > Make Post 2 and assign it to just category A.
 >
 > Do the `category__and => (A, B)` query and compare.
 >
 > The query without GROUP BY will return Post 1 as well as Post 2, which
 is incorrect.

 I tried a clean install and tested this. My change does not return both
 posts, though, and I'm not sure why you'd think it would. The results are
 identical.

 Did you try it? If so, I think you accidentally tried something different.

 It won't include Post 2 because when the outer query calls the inner query
 like this:

 {{{
   AND (
   SELECT COUNT(1)
   FROM wp_term_relationships
   WHERE term_taxonomy_id IN (<A>,<B>)
   AND object_id = <Post_2_ID>
   ) = 2
 }}}

 .... the "COUNT(1)" result won't equal 2. It will equal 1, because only
 one row in wp_term_relationships will match both WHERE/AND clauses. This
 subquery will therefore be false and the post won't be included.

 I'm attaching a patch to make 100% sure we're talking about the same
 change....

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