[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:  accepted
 Priority:  normal        |   Milestone:  3.2
Component:  Performance   |     Version:  3.1
 Severity:  normal        |  Resolution:
 Keywords:  has-patch     |
--------------------------+-----------------------

Comment (by nkuttler):

 FYI, #mysql suggested a "derived table" query which would look like

 {{{
 #!sql
 SELECT a.name, b.name, IFNULL(b.cnt, 0) AS cnt FROM tblname AS a LEFT JOIN
 (SELECT name, COUNT(*) AS cnt FROM tblname2 GROUP BY name) AS b ON a.name
 = b.name;
 }}}

 Obviously not adapted to wp, and I didn't look further into it. I'll do
 two queries for now.

 (and +1 on major bug, this obviously renders a built-in feature useless in
 quite common circumstances)

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