[wp-trac] [WordPress Trac] #24837: querying optimization for category AND searching

[WordPress Trac]

#24837: querying optimization for category AND searching
-------------------------+-------------------------------------
 Reporter:  robertv123   |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Taxonomy     |    Version:  trunk
 Severity:  normal       |   Keywords:  has-patch needs-testing
-------------------------+-------------------------------------
 querying category with an AND for two term_id's plus having a 50,000 +
 posts site causes the query produced by get_sql in wp-
 includes/taxonomy.php to take excessive amount of time.

 On my site the current query in trunk takes 200 + seconds and the
 optimizes query takes 100 to 200 milliseconds.

 Sub selects in mysql are not optimized on the server very well.

 Example code to utilize query
 query_posts(array('category__and' => array($term0_id,$term1_id)));

 affected code starts on line 724 of wp-includes/taxonomy.php

 Potential problems.

 1. I simply added the temp table to the $join variable not knowing if this
 will cause problems on other queries
 2. Clean up of the temporary tables may be needed after query is executed
 with a persistent connection but in my case they can be left there as
 mysql will garbage clean them after script disconnects.

 Attached is a patch to wp-includes/taxonomy.php

--
Ticket URL: <http://core.trac.wordpress.org/ticket/24837>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software