[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:13 scribu]:
 > I ran some tests with SQL_NO_CACHE on (22600+ posts):
 >
 > GROUP BY: 0.9389 sec
 >
 > COUNT: 0.1706 sec
 >
 > JOINs: 0.0013 sec

 Right, that makes sense. The fundamental problem with the first two is
 that the DEPENDENT SUBQUERY runs the inner query once for each outer row.
 My proposed change isn't the fastest possible; it merely makes sure that
 the subquery prefers the index that returns only a handful of rows
 (keeping the inner query manageable), instead of using the index that
 potentially returns tens of thousands of rows requiring a filesort that
 completely kills everything.

 The JOIN method avoids running a DEPENDENT SUBQUERY and is superior, but
 is unfortunately not possible in every case.

 I agree that using JOIN where possible, and my suggestion when it's not,
 seems to be the best possible solution.

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