[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:  defect (bug)  |      Status:  reviewing
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:  3.1
 Severity:  normal        |  Resolution:
 Keywords:  close         |
--------------------------+------------------------------

Comment (by tigertech):

 Replying to [comment:2 scribu]:
 > Sure, they ''seem'' to return the same posts, but they don't. That
 modified query is equivalent to using {{{'category__in'}}}.

 Hmmmm -- what makes you say that? I've tried it with some simpler queries
 than the example (which I unfortunately can't compare directly because the
 original WordPress version never finishes), and it seems to be correct.

 In either version, the {{{'category__and'}}} nature of it comes from
 loading all the rows of  wp_term_relationships that match both the
 object_id and term_taxonomy_id, then counting them and making sure that
 the number of found rows is the same as the number of categories passed
 into the query (2 in this example).

 Even if my suggested change is wrong for a {{{'category__and'}}} fix, I'm
 pretty sure it's not equivalent to {{{'category__in'}}}. Using
 {{{'category__in'}}} on our customer's data with "461" as one of the
 categories always results in at least 167 posts if I remove the LIMIT. But
 using my replacement query with "461" as one of the categories always
 results in fewer.

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