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

WordPress Trac wp-trac at lists.automattic.com
Tue Mar 1 05:13:25 UTC 2011

#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

 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 (
   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

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

More information about the wp-trac mailing list