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

 If you turn on profiling, you see why the first two methods are so slow:

 {{{
 Sending data    0.000031
 executing       0.000003
 Sending data    0.000007
 executing       0.000002
 }}}

 over and over again.

 So, anyway, we can use both patches:

 [attachment:16706.diff] avoids 'AND' queries whenever possible.

 [attachment:16706-tigertech.diff] optimizes the SQL for 'AND' queries when
 we have no choice.

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