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

Comment (by tigertech):

 By the way, I showed this explanation to a co-worker here who said it
 wasn't clear why the second version is so much faster. So:

 The reason it's faster is that it can operate almost entirely on the
 wp_term_relationships index. For each row (post) in the outer query, the
 inner query uses the PRIMARY index to load a very small number of rows
 based on the object_id. So it ends up with something like five rows, which
 it can then examine to see if they match "term_taxonomy_id IN (1,461)". (A
 single index on "(object_id, term_taxonomy_id)" would probably speed this
 up even more and eliminate the "Using where" on the subquery, operating
 entirely on the index.)

 In contrast, the "GROUP BY" version attacks it the other way round: it
 first uses the term_taxonomy_id index to search for rows in
 wp_term_relationships that match "term_taxonomy_id IN (1,461)".
 Unfortunately, it ends up with tens of thousands in the worst case,
 instead of a handful, which it then shoves into a temporary table. It then
 sorts the unindexed temporary table to do the COUNT. That might be fine if
 MySQL 5.0.x only did that once, but it does it once for each outer query
 row, even though the results of the inner query are identical (constant)
 each time through.

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