[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):

 By the way, that's still small enough that MySQL doesn't resort to a
 filesort.

 If you try the same thing when one of the {{{category__and}}} categories
 is a category containing more than, say, 50000 posts, MySQL will switch to
 a filesort and the GROUP BY version of the test will suddenly start to
 take minutes/hours.

 (I'm not suggesting you actually do it -- just emphasizing that the
 problem can get much, much worse than these results indicate.)

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